Power BI grand total on top N wrong percent

1
votes

I am running in this issue: I want to display top 5 order on items list, and show the overall %.

enter image description here

As you can see on the left table, item H has 60 orders and an overall of 21.05% Now I want to see only the top 5, but with the same % overall and not 28.57% as you can see on the right table.

The filter is pretty easy.

enter image description here

I guess it can be resolved using DAX, but can't find the correct formula.

Please help.

2
powerbidax

2 Answers

1
votes

The implicit measure %GT orders is designed to sum to 100%. You'll need to write your own measure if you want it to behave as you describe.

The measure would look something like this:

Percent Orders =
DIVIDE(
    SUM(Table1[orders]),
    CALCULATE(
        SUM(Table1[orders]),
        ALL(Table1)
    )
)

The implicit measure %GT orders you have is essentially using ALLSELECTED() instead of ALL(). You want the latter if you want to ignore your top N filter in the denominator.

1
votes

It seems that the %GST order is a measure that is calculated based on the data available to it. You might want to add a calculated column with the values calculated based on the data as follows

%GST = Table1[Orders]/sum(Table1[Orders])

then you might use the filter based on orders and see the data accordingly. See the images below. Hope this helps.

New Column

reports view