Forum Discussion

stepantuzil's avatar
stepantuzil
Regular Visitor
2 years ago
Solved

Cumulative Sum with changing division

Hello, 

I'm trying to divide cummulativ sum with value from another column.
I got rigth the cummulativ sum, but if I try to divide it, it divide the whole sum..

Data:

Calendar Date SUM_RT  Sum_sales  SUM_RT_w_correction  Sum_sales_correction MAX_correctionExpected Measure 
14.09.202082800828008280082800182800
01.10.2020828000828000182800
02.10.202016560082800165600828001165600
05.10.2020165600016560001165600
06.10.20203588001932003588001932001358800
12.10.2020358800035880001358800
13.10.202041400055200414000552001414000
15.10.20205520001380005520001380001552000
31.05.2021552000055200001552000
01.06.202160720055200607200552001607200
03.06.202169000082800690000828001690000
30.11.2021690000069000001690000
01.12.20218280001380008280001380001828000
01.02.2022828000082800001828000
02.02.20229660001380009660001380001966000
28.07.2022966000096600001966000
29.07.202210212005520010212005520011021200
02.08.202210488002760010488002760011048800
30.08.2022104880001048800011048800
31.08.202211316008280011316008280011131600
07.09.2022113160001131600011131600
08.09.202211868005520011868005520011186800
20.10.20221324800138000132480013800011324800
09.12.2022132480001324800011324800
12.12.202213524002760013524002760011352400
05.01.2023135240001352400011352400
06.01.20231462800110400146280011040011462800
06.03.202314904002760014904002760011490400
09.03.202315456005520015456005520011545600
22.03.202316284008280016284008280011628400
05.04.202316836005520016836005520011683600
19.04.2023168360001683600011683600
20.04.202317664008280017664008280011766400
02.05.20231876800110400187680011040011876800
12.05.2023187680001876800011876800
15.05.202319596008280019596008280011959600
22.06.2023195960001959600011959600
23.06.202320148005520020148005520012014800
25.08.20232180400165600218040016560012180400
14.09.2023218040002180400012180400
15.09.202322632008280022632008280012263200
22.09.2023226320002263200012263200
25.09.202323460008280023460008280012346000
19.10.2023234600001173000022346000
20.10.202323736002760011868001380022359800
26.10.2023237360001186800022359800
27.10.202324012002760012006001380022373600
29.11.2023240120001200600022373600
30.11.202324564005520012282002760022401200
19.01.2024245640001228200022401200
22.01.20242597236,4140836,41298618,270418,222471618,2
02.02.20242597236,401298618,2022471618,2
05.02.20242653570,9656334,561326785,4828167,2822499785,48


The cummulative sum measure and with the division in the graph:

 

The sum_sales_correction column has the rigth values, but I'm not able to sum them.
Can you please help me?

Thank you very much.

 

 

 

SUM_RT_w_correction = 
CALCULATE(
    SUM('LISA PBI Development of Sales'[Sales])/[MAX_correction],
    FILTER(
        ALL('LISA PBI Development of Sales'[Calendar day.Calendar day Level 01]),
        'LISA PBI Development of Sales'[Calendar day.Calendar day Level 01] <= MAX('LISA PBI Development of Sales'[Calendar day.Calendar day Level 01]) && 
        'LISA PBI Development of Sales'[Calendar day.Calendar day Level 01] >= MAX('PPC Project Materials'[Start of contract])
    )
)
MAX_correction = 
CALCULATE(
    MAX(Korekce[Hodnota]), 
    FILTER(
        ALL(Korekce[Atribut]),
        Korekce[Atribut] <= MAX('LISA PBI Development of Sales'[Calendar day.Calendar day Level 01])
    )
)

 

 

 

Need Help
  • stepantuzil's avatar
    stepantuzil
    2 years ago

    Hello, 
    I solved the problem. I used this measure:

    _Divide SUM = SUMX(Values('LISA PBI Development of Sales'[Calendar day.Calendar day Level 01]), Divide([Sum_sales],[MAX_correction]))

    Inside the cummulative measure:

    SUM_RT_w_correction = 
CALCULATE(
    [_Divide SUM], 
    FILTER(
        ALL('LISA PBI Development of Sales'[Calendar day.Calendar day Level 01]),
        'LISA PBI Development of Sales'[Calendar day.Calendar day Level 01] <= MAX('LISA PBI Development of Sales'[Calendar day.Calendar day Level 01]) && 
        'LISA PBI Development of Sales'[Calendar day.Calendar day Level 01] >= MAX('PPC Project Materials'[Start of contract])
    )
)

    Result in graph: 



    Have a nice day.

2 Replies

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    2 years ago

    Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

    Do not include sensitive information or anything not related to the issue or question.

    If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216

    Please show the expected outcome based on the sample data you provided.

    Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

  • stepantuzil's avatar
    stepantuzil
    Regular Visitor
    2 years ago

    Hello, 
    I solved the problem. I used this measure:

    _Divide SUM = SUMX(Values('LISA PBI Development of Sales'[Calendar day.Calendar day Level 01]), Divide([Sum_sales],[MAX_correction]))

    Inside the cummulative measure:

    SUM_RT_w_correction = 
CALCULATE(
    [_Divide SUM], 
    FILTER(
        ALL('LISA PBI Development of Sales'[Calendar day.Calendar day Level 01]),
        'LISA PBI Development of Sales'[Calendar day.Calendar day Level 01] <= MAX('LISA PBI Development of Sales'[Calendar day.Calendar day Level 01]) && 
        'LISA PBI Development of Sales'[Calendar day.Calendar day Level 01] >= MAX('PPC Project Materials'[Start of contract])
    )
)

    Result in graph: 



    Have a nice day.