Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
Solved! Go to Solution.
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.
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.
Thank you @v-juanli-msft ,
It is working as expected.
But I want to display cummulative percentage instaed of sum.
Any suggestion in DAX.
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 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.
Hi,
I am using all measure
cumWeekRev=CALCULATE (
Rev[ revenue]
FILTER (
ALLSELECTED ( 'Date'),
'Date'[WeekSortNum] <= MAX ( 'Date'[WeekSortNum] )
)
)
User | Count |
---|---|
128 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |