Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago

Wrong Subtotal when using Variable

Hello,   I get a wrong result in my subtotal when I utilize a variable.  I use months as a filter. I cannot see the connection.     Diff Actual Billing - Budget = Var DiffActualtoBudget = [To...
Need Help
v-frfei-msft's avatar
v-frfei-msft
Community Support
7 years ago

Hi Anonymous,

 

Could you please tell me what are your measures of [Total Actual Billing] and [Total Budget] and kindly share your sample data if possible?

 

Regards,

Frank

Anonymous's avatar
Anonymous
Not applicable
7 years ago

wefwefwef

 

Hi v-frfei-msft

 

I cannot share data. 

 

However, I discovered the pattern of the wrong calculation and perhaps you could explain why Power BI behaves so...

 

Please see snapshot...

 

When I use the VAR expression, the Total is actually multiplied by 12.

 

If I choose only 3 months, it then multiplies the actual total by 3 and so on...

 

Something in the way that the SUMX is iterating over the VAR expression is the cause for the multiplication in the number of presented rows.

 

This is my workaround solution:

 

Diff Forecast - Actual w VAR = 
Var DiffForecastToActual = [Total Forecast Fees] - [Total Actual Billing]
Var CountVisibleRows = COUNTROWS(VALUES(dimCalendar[MonthYear]))
RETURN

IF(
    HASONEVALUE(dimCalendar[MonthYear]),
    DiffForecastToActual,
    DIVIDE(
        SUMX(
        VALUES(
            dimCalendar[MonthYear]),
            DiffForecastToActual),
            CountVisibleRows)      )

Does that Make sense?

 

Regards,

Tamir