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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Anonymous
Not applicable

How to create a measure with division and have a correct total in pivot table?

I work with the tabular model which has a fact table and lots of dimension tables with many attributes. I've added a new measure with division operation and noticed that the total numbers are incorect for this measure and for measures that uses the measure in their DAX expressions. 
For example:

Row labelsMeasure 1Measure 2Measure 3 (Measure 1 / Measure 2) 
Attribute value 1102510 / 2 = 5
Attribute value 28428 / 4 = 2
Attribute value 36236 / 2 = 3
Grand Total2483 (I want to have 10)24 / 8 = 3 


Here you can see that grand total correct for Measure 1 (10 + 8 + 6 = 24) and for Measure 2 (2 + 4 + 2= 8), but not for Measure 3 (5 + 2 + 3 not equal to 3). Year, I know that pivot table calcualtes rows one by one, and gets when 24 divides by 8. Because of this I changed the measure to this and got correct result for specific dimension attribute:   

 

Measure := 
VAR division = [Measure 1] / [Measure 2]
VAR summirizedTable= SUMMARIZE('DimensionTable', DimensionTable [Attribute], "MeasureGroupedByAttribute",  [Measure 1] / [Measure 2])
RETURN 
IF ( HASONEFILTER(DimensionTable [Attribute]), division, SUMX(summirizedTable, [MeasureGroupedByAttribute]))

 


It works for the specific DimensionTable [Attribute], but I need the correct result for all dimensions and their attributes or all possible dimension combinations (in case if user selects several dimensions). Aparently this approach works only for static reports with fixed dimensions, but can't be used for a pivot table.
Is it possible to implement a measure (or change the model) to get correct results for all dimensions in pivot table?
If it is not possible should I use a calculated table filled with this measures values?
Or maybe calculation groups could help me in this case?

2 REPLIES 2
amitchandak
Super User
Super User

@Anonymous , Try like

 

Sumx(SUMMARIZE('DimensionTable', DimensionTable [Attribute], "MeasureGroupedByAttribute", Divide([Measure 1], [Measure 2])),[MeasureGroupedByAttribute])

 

 

Please provide your feedback comments and advice for new videos
Tutorial Series Dax Vs SQL Direct Query PBI Tips
Appreciate your Kudos.

Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube
Anonymous
Not applicable

@amitchandak Probably I wasn't clear enough, a user in pivot table can select any of dimension attribute (or combination of attributes) and I can't predict what attribute will be selected. In your proposed example, there is the hard-coded attribute, but I need somehow dinamicaly get the attribute which was selected and use it for measure calculation.

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

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

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.