cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
BVdL Frequent Visitor
Frequent Visitor

calculating percentage of grand total of a total per customer

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:

 

Capture.PNG

 

 

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!

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: calculating percentage of grand total of a total per customer

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:

 

Top_3.png

 

Regards,

MFelix

 



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




2 REPLIES 2
Super User
Super User

Re: calculating percentage of grand total of a total per customer

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:

 

Top_3.png

 

Regards,

MFelix

 



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




BVdL Frequent Visitor
Frequent Visitor

Re: calculating percentage of grand total of a total per customer

Thanks a lot, this worked like a charm!