cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
DAX_Learner07 Regular Visitor
Regular Visitor

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.

 

Matrix.PNG

 

 

 

 

Actual value for march is as shown

 

MArch.PNG

 

Any help/suggestion would be appreciated.

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Cumulative sum/total in Matrix visualization

Hi @DAX_Learner07 

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

6.png

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.

View solution in original post

4 REPLIES 4
Community Support Team
Community Support Team

Re: Cumulative sum/total in Matrix visualization

Hi @DAX_Learner07 

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.

DAX_Learner07 Regular Visitor
Regular Visitor

Re: Cumulative sum/total in Matrix visualization

Hi,

 I am using all measure

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


Community Support Team
Community Support Team

Re: Cumulative sum/total in Matrix visualization

Hi @DAX_Learner07 

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

6.png

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.

View solution in original post

Highlighted
DAX_Learner07 Regular Visitor
Regular Visitor

Re: Cumulative sum/total in Matrix visualization

Thank you @v-juanli-msft ,

It is working as expected.

But I want to display cummulative percentage instaed of sum.

 

Any suggestion in DAX.

 

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 59 members 1,155 guests
Please welcome our newest community members: