PowerBI DAX – VAR

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

Subscribe