Forum Discussion

TylerVono's avatar
TylerVono
Icon for Helper III rankHelper III
3 years ago

Paredo Chart - Cumulative Total

Hello,

 

I need help making a Paredo Table then chart, a cumulative total measure then Total measure I was following this guide but I am stuck: https://powerbi.tips/2016/10/pareto-charting/

 

Here are my equations below. The formula I tried was: 

 

CumulativeTotal = calculate(SUMX(ALL('Table'[Number]),filter(allselected('Table'[Number]),[Rank]<=[Rank]))) . It is not working.

 

Count = COUNT('Table'[Number]) (The reason I have to Count is that my 'Number' value is actually a Purchase order number (EX: 1584594) the error I find is that the mesaure continues to SUM each PO as if it was a total. Rather - They are individual and unique. They need to be counted as single and distinct, so I have to Distinctly Count them.
 
Rank = if(isinscope('Table'[Category]),rankx(allselected('Table'[Category]),'Table'[Count]))
 
Table = summarize('Main 1 2 2023 to 2 18 2023','Main 1 2 2023 to 2 18 2023'[Category],'Main 1 2 2023 to 2 18
2023'[Number],'Main 1 2 2023 to 2 18 2023'[Classification],'Main 1 2 2023 to 2 18 2023'[Item],'Main 1 2 2023 to 2 18 2023'[Supplier],'Main 1 2 2023 to 2 18 2023'[Planning Team])
 
Any ideas?
 

 

Need Help
Tips and Tricks

14 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Icon for Super User rankSuper User
    3 years ago

    Hi,

    I am not sure how your datamodel looks like, but I tried to create a sample pbix file like below.

    Please check the below picture and the attached pbix file.

    I hope the below can provide some ideas on how to create a solution for your datamodel.

     

     

     

    cumulate measure: =
CALCULATE (
    COUNTROWS ( Data ),
    WINDOW (
        1,
        ABS,
        0,
        REL,
        ALL ( Category[Category] ),
        ORDERBY ( CALCULATE ( COUNTROWS ( Data ) ), DESC )
    )
)

     

     

     

     

    • TylerVono's avatar
      TylerVono
      Icon for Helper III rankHelper III
      3 years ago

      It actually works but here is the weird thing, there is still a red underline:

      yet is works!

       

      • Jihwan_Kim's avatar
        Jihwan_Kim
        Icon for Super User rankSuper User
        3 years ago

        Hi,

        Thank you for your message.

        The ORDERBY DAX function has recently been updated, and perhaps this could be the reason why it showed like that. 🙂