Cumulative total values showing wrong output.

[Anonymous]

Hi All,
I have created cumulative total measure but it didn’t work for me.
measure =

CALCULATE(

       DIVIDE(

        SUM(table[viewingDuration]),

        calculate(

        SUM(table[viewingDuration]),

           ALL(table[SearchnetSections])))

               *

                distinctCOUNT(table[RowId]),

    FILTER(

        ALL(table[SearchnetSections]),

        table[SearchnetSections] <= max(table[SearchnetSections])))

Cumulative total should work on last column 2814.03 + 2501.35
but the output result is different than expected in weighted by viewing time column
as we can see 2nd values is showing the wrong output 8656.55

Need help to resolve this issue.

[v-yanjiang-msft]

Hi @Anonymous ,

According to your description, I have two suggestions for your formula.

First, there is actually no accumulation logic in your formula "CALCULATE( DIVIDE(a,b)*c, FILTER())", for accumulation, the most common formula is "CALCULATE(SUM(),FILTER())" or "SUMX(FILTER(),expression)".

Second, if you want to get the total value of [viewingDuration] in the whole table, it will not get the correct result by your formula "CALCULATE ( SUM ( table[viewingDuration] ), ALL ( table[SearchnetSections] ) )", because of the table filter in your formula, it should be "CALCULATE ( SUM ( table[viewingDuration] ), ALL ( table) )".

Here's my solution, create a measure.

Measure = 
SUMX (
    FILTER (
        ALL ( 'table' ),
        'table'[SearchnetSections] <= MAX ( 'table'[SearchnetSections] )
    ),
    CALCULATE (
        DIVIDE (
            SUM ( 'table'[viewingDuration] ),
            CALCULATE ( SUM ( 'table'[viewingDuration] ), ALL ( 'table' ) )
        )
            * DISTINCTCOUNT ( 'table'[RowId] )
    )
)

I create a sample and get the correct result.

I attach my sample below for reference.

Best Regards,
Community Support Team _ kalyj

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

[amitchandak · Accepted solution]

@Anonymous , In case you need sum then try like

CALCULATE(

Sumx(Values(SearchnetSections) ,calculate( DIVIDE(
SUM(table[viewingDuration]),
calculate(
SUM(table[viewingDuration]),
ALL(table[SearchnetSections])))
*
distinctCOUNT(table[RowId]) )),
FILTER(
ALL(table[SearchnetSections]),
table[SearchnetSections] <= max(table[SearchnetSections])))