A very useful way to work with DAX functions is to use VAR. VAR is a form of variable in which results can be stored temporarily without having to create measures and then using them in others. For example you use Var to store the result of a particular group and then use the var output to calculate something similar to Percentage total
The below DAX formula shows an example of the above calculation
PercentEarning =
VAR YearlyRevenue = Sum('Fact Order'[Total Excluding Tax])
VAR TotalRevenue = CALCULATE(SUM('Fact Order'[Total Excluding Tax]),ALL('Dimension Date')) return if(TRUE(),YearlyRevenue/TotalRevenue)
The screenshot below shows what it would look like inside PowerBI
The SQL equivalent of the above DX function is similar to the query below
SELECT @var
DECLARE @var FLOAT = (
SELECT sum([Total Including Tax])
FROM fact.[order] o
WHERE [Order Date Key] IN (
SELECT DATE
FROM Dimension.DATE
WHERE [Calendar Year] = '2013'
AND [Calendar Month Number] = 2
)
) / (
SELECT sum([Total Including Tax])
FROM fact.[order] o
WHERE [Order Date Key] IN (
SELECT DATE
FROM Dimension.DATE
WHERE [Calendar Year] = '2013'
)
)
SELECT @var
Please Consider Subscribing
