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.
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!
User | Count |
---|---|
22 | |
11 | |
8 | |
6 | |
6 |
User | Count |
---|---|
25 | |
12 | |
11 | |
7 | |
6 |