how to calulate cumulative %

[Anonymous]

Hi Team,

I have below requirement

i Have 2 dimension tables(Month and class) and one fact table(FactA)

Facttabe has below data

ID   Monthid        MaterialId       Class      Value

1     1                    1                     New        100

2     2                     1                     Old          50

3      3                    1                    Excess      110

Like so we have facttable data

Monthdimesnion has 

Monthid Monthname year

1             Jan               2020

2             Feb              2020

3             Mar              2020

Class dimesion has below

Clssid   Classname

1          New

2          Old

3         Excess

My report must show data in below format

slicer1             slicer 2               sicer2

Clas                Selectmonth      previousmonth(this can be any month )

Table data

MaterialName    SelectClass     PreviousClass   SelectMonth   PreivousMonth  Sleectvalue  prevous value Diff   Cumulative%

AAAA                   New               Old                  Mar2020          jan2020               110             100            10           <Value>

Like this the data has to display

I achived till diff column but how to get cumulative % 

Since i am using Monthid column to slicer both selectmonth and previousmonth and also Value column to show both selectvaue previousvaue 

I reated 2 fact tables and achived the above solution upto Diff.But i am not able to find Cumulative% 

Kindly do needful

[Anonymous]

@Anonymous 

It is appreciated if you can share a link of a sample pbix, it is difficult for us to accurately reproduce your model with current relationship, and including the "diff column" you created. 

In addition, your expected report is pretty vague to me. With Power Bi, It is not possible to create a table visual filtered by 2 month slicers, and of course not possible to show the current and previous month data on the same row.

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

[AllisonKennedy]

@Anonymous  How do you define cumulative %? What do you want to divide by? You will most likely need to use CALCULATE and ALL functions, but perhaps an ALLEXCEPT or other option depending on your requirements.

Has this post solved your problem? Please mark it as a solution so that others can find it quickly and to let the community know your problem has been solved. 

If you found this post helpful, please give Kudos.

I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query. 

https://sites.google.com/site/allisonkennedycv

[Anonymous]

I have tried with calculate function to get cumulative totals for diff column.

but the values are not showing as expected since diff is a measure and i am using same monthid column for both current selection and previous selecton slicers ...can you ple help me on this...

if you need pbix file i can upload 

[AllisonKennedy]

pbix file is always helpful, but what value do you expect for cumulative % in your above example? Is it diff/current month?

Has this post solved your problem? Please mark it as a solution so that others can find it quickly and to let the community know your problem has been solved. 

If you found this post helpful, please give Kudos.

I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query. 

https://sites.google.com/site/allisonkennedycv