Need to find the % based on Cumulative total Column

[Anonymous]

Hi Guys,

I have table as below, I need to calculate Cumulative Total for each Type for Columns "FNet" and "FLevel" but as a calulcative Columns and not as measure. Since i need to add these columns as Slicer. 

I have created a Measure, but i need to convert the same as Calculative Columns,

Cumulative = CALCULATE(SUM('Table'[FNet]), FILTER( ALLSELECTED('Table'[Valid From]), ISONORAFTER('Table'[Valid From], MAX('Table'[Valid From]), DESC))

Please Help me to get the same Calculation in Calculative Column.., 

Type	Valid From	FNet	FLevel
Base	1/10/2020	100	20
Base	1/17/2020	200	30
Base	1/24/2020	50	0
Skip	1/10/2020	0	100
Skip	1/17/2020	500	0

[amitchandak]

Make sure you have a date table and join it with your table. And try like this example

Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(date,date[date] <=maxx(date,date[date])))

% of GT

Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(date,date[date] <=maxx(date,date[date])))/SUMX(all(Sales),Sales[Sales Amount]),

 In case you are looking for sub total or % of sub total refer

https://community.powerbi.com/t5/Desktop/Percentage-of-subtotal/td-p/95390

[Anonymous]

@amitchandak Thanks for your reply. Based on the formula which you provided, i created the Cumulative Column for "FNet" and it works perfectly but if i try the same formula for "FLevel" i am getting the error as "Circular dependency"

Column 1 = CALCULATE(SUM(Table[FNet]),filter('Date','Date'[date] <=maxx('Date','Date'[date])))

Column 2 = CALCULATE(SUM(Table[FLevel]),filter('Date','Date'[date] <=maxx('Date','Date'[date])))

% should be calculated based on Column 2/Column 1 - I believe if Column 2 is sorted out the % can be calculated easily.

[amitchandak]

Both of them should be measures. Do you need a column?

A column will like below, But you can not use measure in that

Cumm column = sumx(filter(table,table[Valid From]<=earlier(table[Valid From])),table[FLevel])

[Anonymous]

@amitchandak i am getting Syntax error " highlited below". 

I need both FNet and FLevel as COLUMN which shows Cumulative Value. When i try to create it as column for both FNet and FLevel it shows as Circular Dependency.

Column 1 = sumx(filter('Table','Table'[Date]<=earlier('Table'[Date])),WMAP[FNet])

[amitchandak]

Assuming WMAP is your table. It should be like. Are we taking of 1 table or 2 tables

Column in WMAP = sumx(filter(WMAP,WMAP[Date]<=earlier(WMAP[Date])),WMAP[FNet])

[Anonymous]

@amitchandak Only 1 Table Called wmap.., Below formula is not workly properly ( I mean the result is incorrect) where as the measure formula which you provided is working perfectly. But the Need is to create a Column and not measure.

Column in WMAP = sumx(filter(WMAP,WMAP[Date]<=earlier(WMAP[Date])),WMAP[FNet])

[amitchandak]

If possible please share a sample pbix file after removing sensitive information.Thanks.

[Anonymous]

@amitchandak Sorry for silly question,  iam unable to upload pbix file. I dont have any options to attach file. May i know how to do it./?

[amitchandak]

drop box, one drive, share link

[Anonymous]

@amitchandak I Created a Test file.., Created two measures as Cumulative FNet, Cumulative Flevel and divided both columns..,

Now I need to those two measures "Cumulative FNet and Cumulative Flevel as a Column so that i can use it as Filters.

https://drive.google.com/drive/folders/1Fh9MHjmCrxT1e2BegSndCSgEcT0LeAt8?usp=sharing

[amitchandak]

The formulas are working. But there are nonunique dates you need to add an index column. I can not add as edit query will look for source. Refer :https://yodalearning.com/tutorials/learn-how-create-index-columns-using-power-query/

Please find formula with and without index columns

Cumm Flevel = sumx(filter('Table','Table'[Valid From]<=EARLIER('Table'[Valid From])),'Table'[Flevel])
Cumm FNet = sumx(filter('Table','Table'[Valid From]<=EARLIER('Table'[Valid From])),'Table'[Fnet])


Cumm Flevel = sumx(filter('Table','Table'[Valid From]<=EARLIER('Table'[Valid From]) && 'Table'[index]<=EARLIER('Table'[index])),'Table'[Flevel])
Cumm FNet = sumx(filter('Table','Table'[Valid From]<=EARLIER('Table'[Valid From])  && 'Table'[index]<=EARLIER('Table'[index])),'Table'[Fnet])

[Anonymous]

@amitchandak Sorry i am not getting the correct result with the formula without index. (Attached the file for your reference along with correct result. Not sure where iam making mistake..,.  I also attached the test source file for your reference, Please help.

Also i tried with Index, but it takes huge time and not working at the end with test file.

https://drive.google.com/drive/folders/1Fh9MHjmCrxT1e2BegSndCSgEcT0LeAt8

[Anonymous]

@amitchandak any help please, awaiting for your reply.., I incorporated the formula and it shows incorrect data.., 1st two Column Value is Correct (based on Measure, but i dont need measure) where as last two Column values are incorrect.

ly.

[amitchandak]

@Anonymous ,

I will try in the next few hours and get back to you. Thanks for the reminder

[Anonymous]

@amitchandak any update on this?

@v-gizhi-msft 

[amitchandak]

I used the quick measure option and created a running total. Check if it helps