Forum Discussion

andrew_tran's avatar
andrew_tran
Frequent Visitor
1 year ago
Solved

Cumulative sum based on another measure

Hi all,

 

I am trying to calculate the cumulative sum based on another measure, and this cumulative sum must be responsive to all the applicable filters. I have consulted and tried the methods suggested in the following threads but all were not successful.

 

Solved: Cumulative sum Based on Measure - Microsoft Fabric Community

Solved: Cumulative of a measure based on Ranking - Microsoft Fabric Community

Solved: A Measure to Calculate the Cumulative Sum of Anoth... - Microsoft Fabric Community

 

Data Model

 

CategoryProductCountryDateValue
A1USA20/3/20255695
A2USA20/3/20259512
A3USA20/3/20255327
A4USA20/3/20251894
B5USA20/3/20258015
B6USA20/3/20258947
B7USA20/3/20254884
B8USA20/3/20253406
C9USA20/3/20254986
C10USA20/3/20258608
C11USA20/3/20259774
C12USA20/3/20259005
D13USA20/3/20256792
D14USA20/3/20252992
D15USA20/3/20251407
D16USA20/3/20255108
A1UK19/3/20259448
A2UK19/3/20251192
A3UK19/3/20256055
A4UK19/3/20253093
B5UK19/3/20254799
B6UK19/3/20258402
B7UK19/3/20256724
B8UK19/3/20253346
C9UK19/3/20258850
C10UK19/3/20255656
C11UK19/3/20258535
C12UK19/3/20253370
D13UK19/3/20254926
D14UK19/3/20259484
D15UK19/3/20252792
D16UK19/3/20251849
A1USA19/3/20252817
A2USA19/3/20258992
A3USA19/3/20256510
A4USA19/3/20259940
B5USA19/3/20251058
B6USA19/3/20253414
B7USA19/3/20251509
B8USA19/3/20251022
C9USA19/3/20257779
C10USA19/3/20251505
C11USA19/3/20255895
C12USA19/3/20256569
D13USA19/3/20254569
D14USA19/3/20251429
D15USA19/3/20255218
D16USA19/3/20251434
A1UK19/3/20251201
A2UK19/3/20255574
A3UK19/3/20254106
A4UK19/3/20254376
B5UK19/3/20253175
B6UK19/3/20256792
B7UK19/3/20253382
B8UK19/3/20257139
C9UK19/3/20256033
C10UK19/3/20252514
C11UK19/3/20252613
C12UK19/3/20256610
D13UK19/3/20253502
D14UK19/3/20253792
D15UK19/3/20258942
D16UK19/3/20253233

 

 

Formulas for Sales := Sales = SUM(FctTransaction[Value])

Formulas for Sales Contribution % := Sales Contribution % = DIVIDE([Sales],[Sales Total])

Formula for Rank :=  Sales Contribution % Rank = IF(ISINSCOPE(FctTransaction[Category]),RANKX(ALLSELECTED(FctTransaction[Category]),[Sales Contribution %],,DESC))

 

Expected Result

 

Actual Results

Formular for cumu. 1 := 

Sales Contribution % Cumulative 1 =
SUMX(
    FILTER(ALLSELECTED(FctTransaction)
        ,[Sales Contribution % Rank]<=MAXX(ALLSELECTED(FctTransaction),[Sales Contribution % Rank])
    )
    ,[Sales Contribution %]
)

 

Formular for cumu. 2 := 

Sales Contribution % Cumulative 1 = 
Sales Contribution % Cumulative 2 =
VAR CurrentRank = [Sales Contribution % Rank]
RETURN
SUMX(
    TOPN (
        [Sales Contribution % Rank]
        ,CALCULATETABLE(VALUES(FctTransaction[Category]),ALLSELECTED(FctTransaction[Category]))
        ,[Sales Contribution % Rank]
        ,ASC
    )
    ,[Sales Contribution %]
)
 

 

Could any expert help to advise? Thank you so much!!
 
I have also tried to attach the underlying pbix file but was not able to do so, so my apologies.

8 Replies

    • andrew_tran's avatar
      andrew_tran
      Frequent Visitor
      1 year ago

      Thank you so much for this! Sorry for the late response, I haven't had the bandwidth to study your suggestion, will get back to you asap

  • v-echaithra's avatar
    v-echaithra
    Icon for Community Support rankCommunity Support
    1 year ago

    Hi andrew_tran ,

    we wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?
    If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.

     

    Regards,

    Chaithra

    • andrew_tran's avatar
      andrew_tran
      Frequent Visitor
      1 year ago

      Hi Chaithra, I haven't had the bandwidth to review lbendlin's suggestion, will update this thread ASAP

      • v-echaithra's avatar
        v-echaithra
        Icon for Community Support rankCommunity Support
        1 year ago

        Hi @andrew_tran ,

        We wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?
        If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.

         

        Regards,

        Chaithra.

  • v-echaithra's avatar
    v-echaithra
    Icon for Community Support rankCommunity Support
    1 year ago

    Hi andrew_tran ,

    We wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?
    If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.

     

    Regards,

    Chaithra.

  • v-echaithra's avatar
    v-echaithra
    Icon for Community Support rankCommunity Support
    1 year ago

    Hi andrew_tran ,

    As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?
    If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.

     

    Regards,

    Chaithra E.