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,
here is my sample table:
Date | Sales | Categories | ||
01.01.2022 | 123 | Shoes | ||
02.01.2022 | 55 | Shoes | ||
03.01.2022 | 700 | Machine | ||
04.01.2022 | 900 | Computer | ||
05.01.2022 | 900 | Computer |
I need a measure for calculating the sum of each categories:
Date | Sales | Categories | Measure | |||
01.01.2022 | 123 | Shoes | 178 | |||
02.01.2022 | 55 | Shoes | 178 | |||
03.01.2022 | 700 | Machine | 700 | |||
04.01.2022 | 900 | Computer | 1800 | |||
05.01.2022 | 900 | Computer | 1800 |
Mostly I seen PBI-users using a typical calculate with an modifier like Allexcept to get that value:
Measure = Calculate ( SUM ( 'Salestable' [Sales], Allexcept ( 'Salestable', [Categories]))
Is there a alternative way to get to the same result without the usage of calculate? For example with x-aggregated function or a table variable inside a measure?
Thank you very much in advance.
Best.
Solved! Go to Solution.
Hi,
You can use SUMX to get he result without CALCULATE. Something like this:
Proud to be a Super User!
Please try this Measure.
TotalByCategories =
VAR _Table =
FILTER ( ALL ( 'Table' ), 'Table'[Categories] = MAX ( 'Table'[Categories] ) )
RETURN
SUMX ( _Table, 'Table'[Sales] )
Then, the result will look like this.
Also, attached the pbix file as the reference.
Best Regards,
Community Support Team _ Caiyun
If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!
Please try this Measure.
TotalByCategories =
VAR _Table =
FILTER ( ALL ( 'Table' ), 'Table'[Categories] = MAX ( 'Table'[Categories] ) )
RETURN
SUMX ( _Table, 'Table'[Sales] )
Then, the result will look like this.
Also, attached the pbix file as the reference.
Best Regards,
Community Support Team _ Caiyun
If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!
Hi @v-cazheng-msft,
thank you for the *pbix - file.
I have a question:
If I use the table variable inside you measure to create a calculated table:
When I put this as a table variable inside a measure like you did, shouldn't I get "178" only for shoes?
Since you saying MAX[Categories] it should only return "shoes" since its Max in alphabet.
Why would I get this outcome that following outcome:
Hope I was clear.
Thanks.
@ValtteriN , thanks! That was what I was looking for. Seeing that means also it can be used as a filter table inside a measure?
Measure =
var _Table = Allexcept ( 'Salestable', Categories)
return
Sumx (_'Salestable', [Sales])
Best.
Hi,
You can use SUMX to get he result without CALCULATE. Something like this:
Proud to be a Super User!
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 |
---|---|
47 | |
26 | |
19 | |
14 | |
10 |
User | Count |
---|---|
58 | |
50 | |
44 | |
19 | |
19 |