calculating percentage of grand total of a total per customer

[BVdL]

Hello,

I have been searching around the forum and trying out a few solutions for similar problems, but none of them seemed to do the trick completely for me. In Excel, the calculation takes only a few seconds, in Power BI, it proves to be a little more complex. I have created fictive tables and numbers for extra clarity here (removed superfluous info etc). All measures need to be performed in the same table.

I'll try to sketch the situation as thoroughly as possible:

I have a dataset of customer sales information, and I have been able to subract the information above in tables and matrixes separately in Power BI. However, now I need to compare the totals as a percentage: i.e. which percentage of the total sales is bought by the top 3 customers?

It seems impossible to use data from calculated tables on the desktop, and I do not succeed in recreating all the steps in measures and columns.

all I need to do is create a formula to divide the sum of the sales total of the top 3 customers by the total of all sales. I tried to use the DAX 'TOPN' function, but I do not succeed of making a ranking of the customers based on the sales amounts.

it's probably some basic formula I'm missing here, but I have only started working with DAX and Power BI recently, and I'm missing some basic knowledge to fully understand and analyze similar problems posted on this forum before, let alone to project those solutions to my problem.

Thanks!

[MFelix · Accepted solution]

HI @BVdL,

Create two measures:

ranking =
RANKX ( ALLSELECTED ( Sales[Customer] ), CALCULATE ( SUM ( Sales[Qt] ) ) )

Top 3 % =
DIVIDE (
    CALCULATE (
        SUM ( Sales[Qt] ),
        FILTER ( ALLSELECTED ( Sales[Customer] ), [ranking] < 4 )
    ),
    CALCULATE ( SUM ( Sales[Qt] ), ALLSELECTED ( Sales[Customer] ) )
)

You can change the 4 by any number you want and can even make it with a user selection if needed.

Final result below:

Regards,

MFelix

[BVdL]

Thanks a lot, this worked like a charm!