Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Cumulative sum/total in Matrix visualization

Hi folks,

   Need help on below issue.

I am trying to display percentage of cumulative sum in matrix visualization.But it shows equal values in all cells.

 

 

 

 

 

Actual value for march is as shown

 

 

Any help/suggestion would be appreciated.

 

 

Need Help
  • v-juanli-msft's avatar
    v-juanli-msft
    7 years ago

    Hi Anonymous 

    Create a date table, create a relationship between date table and your main data table

    date = ADDCOLUMNS(CALENDARAUTO(),"year",YEAR([Date]),"month",MONTH([Date]),"weeknum",WEEKNUM([Date]),"monthname",FORMAT([Date],"mmm-yyyy"))

    Create a column in this date table

    weekindex = RANKX(FILTER(ALL('date'),'date'[year]=EARLIER('date'[year])&&'date'[month]=EARLIER('date'[month])),[weeknum],,ASC,Dense)

    Create a measure in main data table

    Measure = CALCULATE([revenue],FILTER(ALLSELECTED(Sheet2),Sheet2[year]=MAX(Sheet2[year])&&Sheet2[month]=MAX(Sheet2[month])&&Sheet2[week]<=MAX(Sheet2[week])))

    Best Regards
    Maggie

     

    Community Support Team _ Maggie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

4 Replies

  • v-juanli-msft's avatar
    v-juanli-msft
    Community Support
    7 years ago

    Hi Anonymous 

    Is the percentage value a measure or a column?

    How do you create this?

    Could you share an example?

     

    I need to test on this to find any workaround.

     

    Here are some similar threads

    cumulative running total

    Cumulative Totals Within Categories

     

    Best Regards
    Maggie

     

    Community Support Team _ Maggie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Hi,

       I am using all measure

      cumWeekRev=CALCULATE (
         Rev[ revenue]
          FILTER (
              ALLSELECTED ( 'Date'),
              'Date'[WeekSortNum] <= MAX ( 'Date'[WeekSortNum] )
          )
      )

  • v-juanli-msft's avatar
    v-juanli-msft
    Community Support
    7 years ago

    Hi Anonymous 

    Create a date table, create a relationship between date table and your main data table

    date = ADDCOLUMNS(CALENDARAUTO(),"year",YEAR([Date]),"month",MONTH([Date]),"weeknum",WEEKNUM([Date]),"monthname",FORMAT([Date],"mmm-yyyy"))

    Create a column in this date table

    weekindex = RANKX(FILTER(ALL('date'),'date'[year]=EARLIER('date'[year])&&'date'[month]=EARLIER('date'[month])),[weeknum],,ASC,Dense)

    Create a measure in main data table

    Measure = CALCULATE([revenue],FILTER(ALLSELECTED(Sheet2),Sheet2[year]=MAX(Sheet2[year])&&Sheet2[month]=MAX(Sheet2[month])&&Sheet2[week]<=MAX(Sheet2[week])))

    Best Regards
    Maggie

     

    Community Support Team _ Maggie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Thank you v-juanli-msft ,

      It is working as expected.

      But I want to display cummulative percentage instaed of sum.

       

      Any suggestion in DAX.