Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi Everyone,
Im sure the title is a bit confusing but is there a way I can show a subtotal of sub items even though I only want it to show top 3 for example assume the visualization without any filters would look like this.
Item Colour Value
Coffee Mug
Red 1
Green 5
Blue 3
Black 2
Total 11
Coffee Table
Red 1
Green 2
Blue 10
Black 3
Total 16
Bookcase
Red 1
Green 3
Blue 4
Black 10
Total 18
What I want it to show is only the top 3 sub items of each item , but i want the total to remain the actual total of all sub items. So this is what i want it to show:
Item Colour Value
Coffee Mug
Green 5
Blue 3
Black 2
Total 11
Coffee Table
Blue 10
Black 3
Green 2
Total 16
Bookcase
Black 10
Blue 4
Green 3
Total 18
I have a measure that just does a simple count of items.
and I have another measure for ranking:
top_3_measure = rankx(all('table'[Colour]), CALCULATE(count('Table'[Item])))
I then use this filter into the matrix visual level filter and put it as less than 4 to only get the top 3 sub items.
The issue im having right now is the totals only reflective of the sub items shown so it shows as this right now:
Item Colour Value
Coffee Mug
Green 5
Blue 3
Black 2
Total 10
Coffee Table
Blue 10
Black 3
Green 2
Total 15
Bookcase
Black 10
Blue 4
Green 3
Total 17
hi, you can solve your problem with this dax:
ValueAll = IF ( HASONEVALUE ( Table1[Colour] ), SUM ( Table1[Value] ), CALCULATE ( SUM ( Table1[Value] ), ALLEXCEPT ( Table1, Table1[Item] ) ) )
User | Count |
---|---|
95 | |
86 | |
78 | |
72 | |
66 |
User | Count |
---|---|
115 | |
106 | |
85 | |
65 | |
64 |