Wrong Totals in Table and Matrix

[darko861]

Hi community,

I have the following pbix file:

https://www.dropbox.com/s/4o7jizvr07gwwb8/DataTest.pbix?dl=0

I have a column in the table and matrix called Revenue Display which summarizes the data to 47 136 which is wrong. Based on my condition I have also created a filter that displays the revenue if the GM is >=100 %. Both the table and matrix have selected the correct amounts but do not summarize to 198 244 which is the correct total amount for the column Revenue Display. Please note that totals for the month-year level are also incorrect for the matrix. 

How can I re-write my Dax so that it calculates the correct totals?

Thanks for your help!

[Greg_Deckler]

@darko861 First, please vote for this idea: https://ideas.powerbi.com/ideas/idea/?ideaid=082203f1-594f-4ba7-ac87-bb91096c742e

This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907

Also: https://youtu.be/uXRriTN0cfY
And: https://youtu.be/n4TYhF2ARe8

[darko861]

@Greg_Deckler , Does not work, please check my sample file

[darko861]

I've got the table to work with this measure:

Revenue Display2 =

var MonthYear = SUMMARIZECOLUMNS(Company[FTG],DimDate[Month-Year],DimAccounts[Subheader],DimAccounts[Subheader2],"_Value",[Revenue_Filter])

RETURN

IF(HASONEVALUE(Company[FTG]),[Revenue_Filter],SUMX(MonthYear,[_Value]))

But the Matrix generates an error with the above measure, is there a better workaround?

[darko861]

The correct totals for the Matrix visual can be achieved by using this measure:

Revenue Display 3 = SUMX(ADDCOLUMNS(CROSSJOIN(VALUES(Company[FTG]),VALUES(DimDate[Month-Year]),VALUES(DimAccounts[Subheader]),VALUES(DimAccounts[Subheader2])),"XXX",[Revenue_Filter]),[Revenue_Filter])

But again, it doesn't seem optimal as it is slow when using a cross-join. Is there a better solution for both the table and matrix visual?

Your help is much appreciated.

[v-rzhou-msft · Accepted solution]

Hi @darko861 ,

I suggest you to try this code to create a measure.

Revenue Display 2 = 
VAR _ADD =
    SUMMARIZE (
        Data,
        DimDate[Month-Year],
        Company[FTG],
        DimAccounts[Subheader],
        DimAccounts[Subheader2],
        "XXX", [Revenue_Filter]
    )
RETURN
    SUMX ( _ADD, [Revenue_Filter] )

As the above screenshot, the duration of [Revenue Display 2] is only 355 ms, and the duration of [Revenue Display 3] is 11245 ms.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[darko861]

@v-rzhou-msft , hi Rico, you summarized the whole Data table, if you dealing with large data sets, let's say the data table had millions of rows, would you still reference the whole Data table or just some columns from the data table in the Summerize measure? how would that measure look like if the data table had millions of rows?

Thanks!

[darko861]

Thanks, it is much quicker!