Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Cumulative total values showing wrong output.

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.

Need Help
  • amitchandak's avatar
    amitchandak
    4 years ago

    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])))

     

2 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    4 years ago

    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])))

     

  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    Icon for Community Support rankCommunity Support
    4 years ago

    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.