cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Resolver II

## Wrong Totals in Table and Matrix

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?

1 ACCEPTED SOLUTION
Community Support

Hi @darko861 ,

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

``````Revenue Display 2 =
SUMMARIZE (
Data,
DimDate[Month-Year],
Company[FTG],
"XXX", [Revenue_Filter]
)
RETURN

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.

7 REPLIES 7
Super User

@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

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...
Resolver II

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

Resolver II

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

Revenue Display2 =

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?

Resolver II

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

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?

Community Support

Hi @darko861 ,

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

``````Revenue Display 2 =
SUMMARIZE (
Data,
DimDate[Month-Year],
Company[FTG],
"XXX", [Revenue_Filter]
)
RETURN

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.

Resolver II

@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!

Resolver II

Thanks, it is much quicker!

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

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

Top Solution Authors
Top Kudoed Authors