Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Cumulative chart on multiple filters, not dates

Hi Power PI Community

 

I'm having trouble finding a way to generate cumulative total charts. Here is the structure of my data:

 

Project noRegionMarketMargin
132USAA100
432CanadaB200
243235CanadaB150
2432AsiaA2
ads23AsiaB23
43DTUSAB343
234USAC453
345CanadaA344
A234AsiaA23
D45CanadaB24
36457USAB345
57467USAA234
54325AsiaC123
............

 

I want to generate a chart showing the cumulative total Margin for each market/region I select, RANKED in decreasing order of Margin.  For example I might want to show on a graph the cumulative margin for the USA for all 3 markets:

X axis is just the number of projects. Largest margins are to the left.

 

I'm pretty sure there is not easy way to achieve this... Any hints?

 

 

 

Need Help

2 Replies

  • v-xicai's avatar
    v-xicai
    Icon for Community Support rankCommunity Support
    7 years ago

    Hi SRioux ,

     

    You can create measure "Comulative_Margin" to get the cumulative total Margin.

    Comulative_Margin = CALCULATE(SUM(Table1[Margin]),FILTER(ALLSELECTED(Table1),ISONORAFTER (Table1[Margin], MAX ( Table1[Margin]), DESC )))

     

    Then, create Scatter chart to display the cumulative total Margin for each market/region selected, ranked in descend order of Margin. For example, when select USA in Slicer, return the result showing below picture.

     

     

     

     

     

     

     

     

     

    Here is my pbix test file link: https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/Eb3IJv9Au1RKsVTyzGZ9gkwBWxK7Xf2v7FHlPl3gJkkGVQ?e=AfANzW 

     

    Best Regards,

    Amy

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Thanks Amy

       

      The ranking in descending order of Margin is not working. Every dot of a given market should be above the one on its left (strictly increasing function). The X axis of the scatter plot needs to be Number of projects, not project number.