Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi,
My data set is similar as follows.
SaleID | Qty | Configuration |
9810 | 81 | Modualar |
7598 | 37 | Segment |
8757 | 58 | Pieces |
7861 | 102 | Modular |
7783 | 84 | Modular |
6818 | 30 | Modular |
9625 | 84 | Segment |
8257 | 22 | Segment |
8822 | 89 | Modular |
8962 | 12 | Modular |
7904 | 83 | Segment |
7218 | 103 | Pieces |
8899 | 89 | Separate |
If I summarize this as
Row LabelsSum of QtyGrand Total874
Config | Sum of qty |
Modualar | 81 |
Modular | 317 |
Pieces | 161 |
Segment | 226 |
Separate | 89 |
I need a filter or slicer of custom discrete percentage value that should select the topN config that contribute to say, 80% of the total.
Else a slider for the percentage value say, 80% to filter the config column based on sum of topN corresponding qty which accounts for the selected percentage of volume.
Two card (say buttons) to be shown stating the above and below discrete aggregate available for selection, that for the selected slicer/filter/slider value.
Lets elaborate,
Row Labels | Sum of Qty | (for ref) |
Modular | 317 | 36% |
Segment | 226 | 26% |
Pieces | 161 | 18% |
Separate | 89 | 10% |
Modualar | 81 | 9% |
Total | 874 | 100% |
So, if a filter/slicer/slider is selected for say 80%,
one card/button to show the "lesser aggregate value is 62%" (is the sum of modular & segment)
another card/button to show the "higher aggregate value is 81%" (is the sum of modular, segment & pieces)
If any one of the two card is selected, the filter has to be applied accordingly.
I don't think the requirement is impractical with so many enthusiasts out here...
Advance thanks for the solution.
Solved! Go to Solution.
Hi @Anonymous ,
In the attached file from you, you have a measure created by you for cumulative % of total.
I used the same measure for my visual and other calculation.
When I used the measure in a filtered table, the measure gets recalculates for the visible range summation which I do not need.
Kindly suggest a way for me to get the cumulative percentage displayed in the right sense.
I need only the filters applied on page and report level (Date, tray size & unit status) to affect and the filter applied on the visual level not to affect the result.
Kindly do the needful.
Additionally, If I use "switch" over the measure you give, I may use the same as indicator say till 80% show as First, next 15% shows as second and remaining 5% shows as last. Is this sounds correct way.
Thanks
Yes @Anonymous ,
You are right. I donot want it to get recalculated on the visible range.
I have converted the same as mentioned by you.
But there is a small addition in the requiremnt. It has to consider the only three filters and other than that to be removed.
The three filters are calender[Date], 'time bucket'[bucket] & Config[Traysize].
So, Kindly help for this first requirement.
Also, another requiremtn is a seperate measure using the above measure to display a text as such F(<=80%), M(>80% & <=95%) and R(>95%).
Please help me with the two above requriemnt.
Thanks
Hi @Anonymous ,
Based on your work, I have modified the formula to suit my need as follows.
Hi @Anonymous ,
The solution is awesome...
Lets change a slight requirement.
Everything your worked around is fine and very handy.. Thanks a lot...
Could you please suggest me how to use the percent filter in the top to filter the table below to its cumulative percentage.
If say 80% is selected, if lesser aggregate is closer than higher aggreate to the selected slicer value, lesser aggregate value filter should be applied.
Similarly if higher aggregate is clocer to the selected slicer value than the lesser aggregate, the vice versa to be happen.
Pl. do the needful.
Thanks in advance
Dear @Anonymous ,
So far, so good.
The final step is to select either lesser/higher aggregate, which in turn filters/multiple selects the configuration column.
Kindly provide workaround for the same.
Thanks in advance
vissvess
User | Count |
---|---|
41 | |
27 | |
23 | |
18 | |
15 |
User | Count |
---|---|
54 | |
35 | |
19 | |
18 | |
15 |