Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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 = [Total Actual Billing] - [Total Budget]
RETURN
//DiffActualtoBudget
IF(
    HASONEVALUE(dimCalendar[MonthYear]),
    DiffActualtoBudget,
    SUMX(
        VALUES(
            dimCalendar[MonthYear]),
            DiffActualtoBudget))    

 

 

 

Please see screenshot. 

 

Thank you,

Tamir

 

 

Incorrect subtotal with variable 2.png

6 REPLIES 6
v-frfei-msft
Community Support
Community Support

Hi @Anonymous,

 

Could you please share your pbix to me so that I can help with that?

 

Regards,

Frank

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.
Anonymous
Not applicable

Hi @v-frfei-msft

 

Unfortunately, I cannot share that pbix file.

 

Can you see clearly the formulas or do I need write it in the thread?

 

Regards,

Tamir

Hi @Anonymous,

 

I made one sample here, However that the measure seemed worked well in my side. Could you please share your sample data to me if possible? And what are the formulas of your [Total Actual Billing] and [Total Budget]?

Capture.PNG

Attached the pbix for your reference.

 

https://www.dropbox.com/s/2rl06yza5z6opmg/Wrong%20Subtotal%20when%20using%20Variable.pbix?dl=0

 

Regards,

Frank

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.
Anonymous
Not applicable

Hi @v-frfei-msft

 

In some measures, I get the same result when I use the VAR expression (as expected ) and in some, I don't (in the subtotal for example).

Perhaps it has to do with the SUMX ...

 

Why would you wrap your simple SUM with a CALCULATE? What is the benefit of that?

 

Regards,

Tamir

 

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

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.
Anonymous
Not applicable

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...

Wrong Subtotal when using VAR in SUMX.PNG

 

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.