Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello community,
First of all, I am not the best developer in the world, as I don't have that background, but I am having a hard time finding a solution to the following problem:
I have a table like this:
Item | A | B | C |
Pear | 10 | 4 | 3 |
Apple | 5 | 2 | 5 |
Orange | 3 | 1 | 4 |
Pear | 8 | 6 | 3 |
Orange | 2 | 1 | 6 |
Apple | 4 | 2 | 9 |
Pear | 7 | 5 | 1 |
and I need to create 1 measure that I can bring into a table or matrix, knowing that each category it has different measures, one for every item like this:
Total Apple = SUM(A) + SUM(B)
Total Orange = SUM(B) + SUM(C)
Total Pear = SUM(A) + SUM(C)
I was successful doing this by adding a custom column at Power Query, but I am wondering if I can develop a DAX measure that can do the same thing.
After some research, I was able to do it combining IF(VALUES(...), however, it returns a table, so I can't insert the measure in a table or matrix when no filter is selected. I also tried using variables 'VAR', but was not successful -I should be doing something wrong-.
I also know that this measure requires line-by-line evaluation, but I am wondering if is any DAX formula or expression that I can use.
Thank you very much in advance
Pablo
Solved! Go to Solution.
Hi @Anonymous ,
Based on my test, you could refer to below measure:
Measure = var a=CALCULATE(SUM(Table1[A]),ALL(Table1))+CALCULATE(SUM(Table1[B]),ALL(Table1)) var b=CALCULATE(SUM(Table1[B]),ALL(Table1))+CALCULATE(SUM(Table1[C]),ALL(Table1)) var c=CALCULATE(SUM(Table1[A]),ALL(Table1))+CALCULATE(SUM(Table1[C]),ALL(Table1)) return IF(CALCULATE(MAX('Table1'[Item]))="Apple",a, IF(CALCULATE(MAX('Table1'[Item]))="Orange",b,c))
Result:
You could also download the pbix file to have a view.
Regards,
Daniel He
Hi @Anonymous ,
Based on my test, you could refer to below measure:
Measure = var a=CALCULATE(SUM(Table1[A]),ALL(Table1))+CALCULATE(SUM(Table1[B]),ALL(Table1)) var b=CALCULATE(SUM(Table1[B]),ALL(Table1))+CALCULATE(SUM(Table1[C]),ALL(Table1)) var c=CALCULATE(SUM(Table1[A]),ALL(Table1))+CALCULATE(SUM(Table1[C]),ALL(Table1)) return IF(CALCULATE(MAX('Table1'[Item]))="Apple",a, IF(CALCULATE(MAX('Table1'[Item]))="Orange",b,c))
Result:
You could also download the pbix file to have a view.
Regards,
Daniel He
Hello Daniel He,
Thank you so much for the response, it works perfectly.
I was struggling with the filters, but your solution works perfectly fine.
Another great lesson in DAX
Use Calculate in your calculated measure to sum values from columns, applying a filter on the item.
e.g. Total Apple = CALCULATE(SUM(A), Item = "Apple") + CALCULATE(SUM(B), Item = "Apple")
Reference: https://docs.microsoft.com/en-us/dax/calculate-function-dax
Regards,
Tarun
Hello Tarun,
thank you for the answer, but I already did it and it is not what I am looking to do.
I want to create just 1 measure that I can bring to a table or matrix and populate each totals withing the same column, not creating 3 different measures with filters.
Sorry if I wasn't very clear earlier
Thank you very much
Pablo
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
95 | |
76 | |
65 | |
51 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |