Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago

pareto

Dear Power BI Community!

I am new to the tool and yet I am challenged by following issue.

I want to show how much of out total sales revenue does our top 20 best-buying customers make. Looks like pareto.

 

1. I ranked TOP 20 customers in cluster bar chart filtered by (limited to) Top 20 revenues.

 

2. Now I need to provide the number what is a % of this TOP20 in our total sales. I like to show in on "Card" visual.

 

3. This might be important: the Total Sales will be also be sliced into countries of sales. So I would need ex. % of TOP 20 customers of US in US total sales, etc in other countries.

 

 

Would you be so nice and help me figuring out the measure that will answer my question, please?

 

Kind regards,

Alex

5 Replies

  • danextian's avatar
    danextian
    Super User
    8 years ago

    Hi Anonymous,

     

    There isn't a native or custom pareto chart in Power BI (how I wish there was one!). There are tutorials on how to make one though. The steps are a little finnicky especially for those who have just started using the tool. v-jiascu-msft has already mentioned a link to a tutorial. Here's another one - https://powerbi.tips/2016/10/pareto-charting/

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      Absolutely, Dale.

       

      Please, find the screenshot of TOP 20 customers (filtered in manual filter TOP N). I want to show what % of total sales they make.

      Then, I would like to be able to provide that information even if I slice the data into localisation. Meaning, I need to show how much of TOP 20 customers in Australia make % total Australian Sales. So the same mechanism, but different denominator (slicer-dependant). 

       

       

       

       

       

       

       

       

       

       

       

       

       

      I would appreciate your help and time

      Thanks

      Alex 

       

      • v-jiascu-msft's avatar
        v-jiascu-msft
        Microsoft Employee
        8 years ago

        Hi Alex,

         

        We can't create a solution without data. But there is a demo in the attachment. Please check it out.

        1. In order to verify the formula, we can try this measure.

        Top20IDs =
CONCATENATEX (
    TOPN ( 20, DimGeography, CALCULATE ( SUM ( FactSales[SalesQuantity] ) ), DESC ),
    [GeographyKey],
    "-",
    [GeographyKey], DESC
)

        2. The formula for % of total sales is as follows.

         

        top20% =
VAR top20Total =
    SUMX (
        ADDCOLUMNS (
            TOPN ( 20, DimGeography, CALCULATE ( SUM ( FactSales[SalesQuantity] ) ), DESC ),
            "Sales", CALCULATE ( SUM ( FactSales[SalesQuantity] ) )
        ),
        [Sales]
    )
RETURN
    DIVIDE ( top20Total, SUM ( FactSales[SalesQuantity] ), 0 )

         

         

         

         

         

         

         

         

         

         

         

        Best Regards,

        Dale