Forum Discussion
Filters, All, AllSelected, AllExcept - Partitioning Data
I have been stuck on a problem for some time now and can't find a related issue in any of the forums.
I've tried to build out solutions with CALCULATE using FILTERs, ALL, ALLEXCEPT, ALLSELECTED, etc. but can't seem to make it work.
I need to have these calculated in a MEASURE, not in a calculated column.
I have a table which looks like this:
| Date | Product | SellerId | PercentOfTime |
| 2020-01-01 | ABC | 20 | 40% |
| 2020-01-01 | ABC | 22 | 55% |
| 2020-01-01 | ABC | 13 | 5% |
| 2020-01-01 | DEF | 20 | 30% |
| 2020-01-01 | DEF | 5 | 60% |
| 2020-01-01 | DEF | 13 | 10% |
| 2020-01-02 | ABC | 20 | 80% |
| 2020-01-02 | ABC | 22 | 20% |
Basically, you can see that for each Date, Product, and SellerId combination, the total PercentOfTime makes up 100%.
I'm trying to get a table which looks like this (below), so I can aggregiate the data and generate the right charts, sorting, etc. Basically, I'm summing the PercentOfTime per SellerId per Date. This column (PercentOfTime_SUM) is easy to generate: it's basically a SUM in DAX, however, I need to be able to generate the Partition_Div column in order to be able to process further calculations.
The Partition_Div column will be the SUM of all PercentOfTime_SUM values partitioned by Date.
**The calculation column is here purely to aid the demonstration: it will not be generated on the UI or anywhere else.
| Date | SellerId | PercentOfTime_SUM | Partition_Div | Calculation of Partition_Div | Result |
| 2020-01-01 | 20 | 70% | 200% | 70+55+15+60 | 35% |
| 2020-01-01 | 22 | 55% | 200% | 70+55+15+60 | 27.5% |
| 2020-01-01 | 13 | 15% | 200% | 70+55+15+60 | 7.5% |
| 2020-01-01 | 5 | 60% | 200% | 70+55+15+60 | 30% |
| 2020-01-02 | 20 | 80% | 100% | 80+20 | 80% |
| 2020-01-02 | 22 | 20% | 100% | 80+20 | 20% |
The objective is to be able to build all of these as measures so that I can divide the PercentOfTime_SUM by the Partition_Div (the Result column) and show the total percent of time each SellerId takes over an entire Date. In plain words, I should be able to say from above, as an example, that SellerId 20 took up 35% of the overall PercentOfTime on 2020-01-01.
I really hope I presented this well. Let me know if you have any questions.
Hi johnnoonan ,
You could refer to my sample for details.
Best Regards,
Zoe ZhiIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
1 Reply
Hi johnnoonan ,
You could refer to my sample for details.
Best Regards,
Zoe ZhiIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.