Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Accumulate values by several dimensions

Hello data warriors! 

I've been trying to solve a problem that is  using a measure that accumulates revenue based on month, day and year. Basically I want to use it in a bar chart with drilldowns, each drilldown will change the dimension, so I intend to make the measure adapt to the dimension shown. 

 

This chart below already shows a measure accumulating only by month, I want transform it into these three dimensions I mentioned. 

 

 


So I tried using this expression:

 

Accumulated =
CALCULATE(
    [06M - Revenue];
    FILTER(
        CALCULATETABLE(
            SUMMARIZE('Cupom'; 'Cupom'[Month Number]; 'Cupom'[Month Name]; Cupom[Year]; Cupom[Day]);
            ALLSELECTED('Cupom')
        );
        ISONORAFTER(
Cupom[Month Number];MAX(Cupom[Month Number]);DESC;
Cupom[Month Name];MAX(Cupom[Month Name]);DESC;
Cupom[Year];MAX(Cupom[Year]);DESC;
Cupom[Day];MAX(Cupom[Day]);DESC
 
        
 
        )
    )
)



But it didn't bring right values. Can someone give me a guidance? Sorry if I weren't clear enough.

Need Help
Show and Tell
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    7 years ago

    Hey TeigeGao ,

    Sorry, what I tried to meant is that the bar chart when the user change the levels of drilldown, it changes based on the dimensions dragged in the object(which is: month, year, day).

    I manage to solve that using another measure that makes a decision structure, for instance: 

    Month accumulative measure: 

    CALCULATE(
        [06M - Revenue];
        FILTER(
            CALCULATETABLE(
                SUMMARIZE('Cupom'; 'Cupom'[Month Number; 'Cupom'[Month Name]);
                ALLSELECTED('Cupom')
            );
            ISONORAFTER(
                'Cupom'[Month Number]; MAX('Cupom'[Month Number]); DESC;
                'Cupom'[Month Name]; MAX('Cupom'[Month Name]); DESC
            )
        )

    Year Accumulative Measure: 
    CALCULATE(
        [06M - Revenue];
        FILTER(
            ALLSELECTED('Cupom'[Year]);
            ISONORAFTER('Cupom'[Year]; MAX('Cupom'[Year]); DESC)
        )
    )
    Day Accumalitive Measure:
    CALCULATE(
        [06M - Revenue];
        FILTER(
            ALLSELECTED('Cupom'[Day]);
            ISONORAFTER('Cupom'[Day]; MAX('Cupom'[Day]); DESC)
        )
    )

    So I created this last measure that verifies if inside the drilldown each one is being filtered, so it accumulates based on what the measure calculates:
    Accumulative Activation:
    F(
    ISFILTERED(Cupom[Month Name); [Month Accumulative Measure];
    IF(
    ISFILTERED(Cupom[Year]);[Year Accumulative Measure];
    IF(
    ISFILTERED(Cupom[Day]); [Day Accumulative Measure];0)
    ))
    Hope you understand.






2 Replies

  • TeigeGao's avatar
    TeigeGao
    Icon for Solution Sage rankSolution Sage
    7 years ago

    Hi Anonymous ,

    Could you please share some sample data and expected result to us for analysis? It's sorry that I can't understand your requirement well. 

    As you mentioned above, "each drilldown will change the dimension", the filter cannot change the dimension, it can only filter data in this dimension.

    Best Regards,

    Teige

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Hey TeigeGao ,

      Sorry, what I tried to meant is that the bar chart when the user change the levels of drilldown, it changes based on the dimensions dragged in the object(which is: month, year, day).

      I manage to solve that using another measure that makes a decision structure, for instance: 

      Month accumulative measure: 

      CALCULATE(
          [06M - Revenue];
          FILTER(
              CALCULATETABLE(
                  SUMMARIZE('Cupom'; 'Cupom'[Month Number; 'Cupom'[Month Name]);
                  ALLSELECTED('Cupom')
              );
              ISONORAFTER(
                  'Cupom'[Month Number]; MAX('Cupom'[Month Number]); DESC;
                  'Cupom'[Month Name]; MAX('Cupom'[Month Name]); DESC
              )
          )

      Year Accumulative Measure: 
      CALCULATE(
          [06M - Revenue];
          FILTER(
              ALLSELECTED('Cupom'[Year]);
              ISONORAFTER('Cupom'[Year]; MAX('Cupom'[Year]); DESC)
          )
      )
      Day Accumalitive Measure:
      CALCULATE(
          [06M - Revenue];
          FILTER(
              ALLSELECTED('Cupom'[Day]);
              ISONORAFTER('Cupom'[Day]; MAX('Cupom'[Day]); DESC)
          )
      )

      So I created this last measure that verifies if inside the drilldown each one is being filtered, so it accumulates based on what the measure calculates:
      Accumulative Activation:
      F(
      ISFILTERED(Cupom[Month Name); [Month Accumulative Measure];
      IF(
      ISFILTERED(Cupom[Year]);[Year Accumulative Measure];
      IF(
      ISFILTERED(Cupom[Day]); [Day Accumulative Measure];0)
      ))
      Hope you understand.