Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
User | Count |
---|---|
124 | |
108 | |
99 | |
62 | |
62 |
User | Count |
---|---|
137 | |
116 | |
102 | |
71 | |
61 |