Power BI - Showing Top 5 records in Metrix Table but total should show for all records

0
votes

I have table with thousands of record. i want to create a table visual that will show top 5 records for each category. i created a measure to achieve this and i am getting the result exactly the same i am looking for but facing one issue there.

See below image where i am showing top 5 records for each category, but after each category i have total.

I don't want that total for top 5 records i am showing in the table instead i want the total of all the records which is there under each category.

How can i achieve that?

Measure I created is - Top 5 = RankX(AllSelected(table(Category), Table(account), table(name)),amount_measure,,,Dense) for Top 5 measure i am putting the filter for top 5.

enter image description here

Category Account Name P% amount country owner
Food A101 AA11 10% 105 India A
Food A102 AA12 20% 120 India A
Food A103 AA13 80% 100 India A
Food A104 AA14 30% 150 India A
Food A105 AA15 60% 90 India A
Stat B101 AA11 10% 205 India A
Stat B102 AA12 20% 220 India A
Stat B103 AA13 80% 200 India A
Stat B104 AA14 30% 250 India A
Stat B105 AA15 60% 190 India A
Admn D101 AD11 10% 305 India A
Admn D102 AD12 20% 320 India A
Admn D103 AD13 80% 300 India A
Admn D104 AD14 30% 350 India A
Admn D105 AD15 60% 290 India A

Thanks, SK

1
powerbi

1 Answers

1
votes

You can try this

Let's suppose you have the following measures

_sumAMT:= SUM('Table 1'[amount])

and this is your ranking measure

_sumAMTRank:= RANKX(ALLEXCEPT('Table 1','Table 1'[Category]),[_sumAMT],,DESC,Dense)

You can revise the subtotal by doing this

_sumAMT by CAT:= CALCULATE(SUM('Table 1'[amount]),ALLEXCEPT('Table 1','Table 1'[Category]))
_revisedTotal:= IF(HASONEVALUE('Table 1'[Name])=true(),[_sumAMT],[_sumAMT by CAT])

Solution