Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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 labels | Measure 1 | Measure 2 | Measure 3 (Measure 1 / Measure 2) | |
Attribute value 1 | 10 | 2 | 5 | 10 / 2 = 5 |
Attribute value 2 | 8 | 4 | 2 | 8 / 4 = 2 |
Attribute value 3 | 6 | 2 | 3 | 6 / 2 = 3 |
Grand Total | 24 | 8 | 3 (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?
@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.
@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.
User | Count |
---|---|
25 | |
12 | |
8 | |
6 | |
6 |
User | Count |
---|---|
26 | |
12 | |
11 | |
10 | |
6 |