Accumulated of a metric

[Anonymous]

Hello,

We want to get the cumulative of a metric.

We have two columns, [Values] and [_Pobla]. From these two columns we need to calculate the division of (Values / _Pobla)*1000 and get the accumulated of this division.

We have generated the following metric, but the result is not as expected because in November, when the population changes, the accumulated value decreases.

_KgHab Cumulated =

var _pob = SUM(Data[_Pobla])

return CALCULATE(DIVIDE(sum(Data[Value]) ,_pob,0)*1000,

FILTER(ALL(Data),

AND(Data[_KeyJoinDate] >= DATE(2020,01,01),

Data[_KeyJoinDate] <= MAX(Data[_KeyJoinDate])

)))

Any ideas? Thanks you

[Anonymous · Accepted solution]

Hi ,

We get the expected objective by making use of the SUMX and the SUMMARIZE function. This is the final metric, in case it can help someone. Thank you all for your contributions.

_KgHab Cumulated =

CALCULATE(
SUMX ( SUMMARIZE(
Data,Data[_KeyJoinDate],"KgHab",DIVIDE(SUM(Data[Value]),SUM(Data[_Pobla]),0)*1000
), [KgHab]),
FILTER(ALLSELECTED('Data'),
AND(Data[_KeyJoinDate] >= DATE(2020,01,01),
Data[_KeyJoinDate] <= SELECTEDVALUE(Data[_KeyJoinDate]))))

Thank you all for your contributions.

[Anonymous]

Hi @Anonymous ,

It's glad to hear that you have gotten the expected result. Could you please mark your post as Answered? It will help the others find the solution easily if they face the same problem with you. Thank you.

Best Regards

[parry2k]

@Anonymous Regardless of the logic, as a best practice, you should always add a date dimension in your model when you working dates and time intelligence functions. Let's follow the best practices and create a scalable solution. You can easily add one by referring to my blog post here Create a basic Date table in your data model for Time Intelligence calculations | PeryTUS IT Solutions

[selimovd]

Hey @Anonymous ,

the way you are writing the code is a little uncommon. Don't do multiple operations in the CALCUALTE statement. Split it up in more understandable parts.

Try the following code:

_KgHab Cumulated =
VAR _pob =
    SUM( Data[_Pobla] )
VAR _val =
    CALCULATE(
        SUM( Data[Value] ),
        FILTER(
            ALL( Data ),
            Data[_KeyJoinDate] >= DATE( 2020, 01, 01 ) && Data[_KeyJoinDate] <= MAX( Data[_KeyJoinDate] )
        )
    )
RETURN
    DIVIDE(
        _val,
        _pob,
        0
    ) * 1000

If that doesn't give you the result you want, can you explain a little better what exactly you want to calculate and what the result should look like.

For example the 1,26 that you marked. What do you expect there as a result and how does the formula for that look like.

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic

[Anonymous]

Hi, 
Thanks for your reply. 

I have tested your code but the result is the same. I try to explain it better:
In the marked data (date = 01/11/2021) = 1.28, it should be 1.49 + 0.11 = 1.60. That is, the kgHab Cumulated value of the previous month plus the _kgHab of 11/01/2021.

[Anonymous]

Hi @Anonymous ,

You can create another new measure to get the pob value first, then update the formula of your measure as below:

Measure = DIVIDE ( SUM ( Data[Value] ), SUM ( 'Data'[_Pobla] ), 0 ) * 1000

_KgHab Cumulated = 
SUMX (
    FILTER (
        ALLSELECTED('Data'),
        AND (
            Data[_KeyJoinDate] >= DATE ( 2020, 01, 01 ),
            Data[_KeyJoinDate] <= SELECTEDVALUE ( Data[_KeyJoinDate] )
        )
    ),
    [Measure]
)

Best Regards

[Syndicate_Admin]

Try this:

CALCULATE(

SUMX(TIME,DIVIDE(sum(value),sum(_Pobla))),

FILTER(

ALLSELECTED(TIME),

ISONORAFTER(TIME[Date], .MAX('TIME'[Date]), DESC)

)

)

I hope it serves you, greetings!

[Anonymous]

Hi @Anonymous 

Thanks for your answer. We still do not get the expected result and I think it is due to the aggregation of the data. In your table you recreate the data that we showed in the first comment of the post, but really our data is this:

_KeyJoinDate = DATE('Data'[Year],'Data'[Month],1)

We calculate the KeyJoinDate column from Year and Month. As you can see, when there is more than one record per month and year, it seems that the SUMX calculates separately and we do not obtain the expected result.

This is the result we get by applying your solution, We get 1.75 for November, instead of the expected 1.6.

Could you help us find the problem?

Thank you