Calculate cumulative percentage from a calculated column

[jessiew]

Hi,

I would like to calculate the Cumulative Qty% like it shows in the excel spreadsheet below. I created a measure for the calculation. But the calculation doesn't seem to be accurate. Can someone point me what is wrong with my measure?

[v-piga-msft]

Hi @jessiew,

I'm not clear about your sample data so I made a test with a sample data similar with yours, but I add an Index column to calculate the cumulative percentage.

Measure =
CALCULATE (
    SUM ( 'Table1'[Column] ),
    FILTER ( ALL ( 'Table1' ), 'Table1'[Index] <= MIN ( 'Table1'[Index] ) )
)

You could have a reference of the formula above.

Hope this can help you!

Best Regards,

Cherry

[jessiew]

Hi,

The excel spreadsheet Sheet 4 indicates the calculation of the cumulative qty%. See below link for the share:

https://drive.google.com/open?id=1EYU9r_EUvXIXpmrMcwqRkh5iivuDgRyT

The pbix file is at below link:

https://drive.google.com/open?id=1T3wi2ENCwqAelFUmxbsftFEtFGRE8a2M

[Ashish_Mathur]

Hi,

Reduce the file size to a few kb's only.  Also, whenever you share a download link, always mention the file size if it it more than 1 MB.  How will i otherwise know the size of the file that i am downloading.

[jessiew]

Hi Ashish_Mathur,

I'm new to this forum. I don't see other peopel who shared the link mentioned the size of the file so I didn't do that.

I tried my best to reduce the file size, but it cannot be only a few KBs. Otherwise the excel sheet and pbix file result will not match.

Here's the new link for the pbix file and excel spreadsheet. 

The pbix file is 1.5MB, the excel spreadsheet is 3.5MB (the excel sheet is zipped). 

The new link for the pbix file is https://drive.google.com/open?id=1ut6hEXd6jqbRUaDA1NUzMXhOsNXLa9iQ 

The new link for the excel spreadsheet is https://drive.google.com/open?id=1ztDDcsthWK-cvAZTuIgOOUvtF_lDt26M (sheet 4 has the Cumulative Qty% calculation).

Thanks!

[Ashish_Mathur · Accepted solution]

Hi,

You may refer to my solution in this PBIX file.

Hope this helps.

[jessiew]

Thanks so much Ashish. It worked. 

[Ashish_Mathur]

You are welcome.

[Ashish_Mathur]

Hi,

Please share the download link of the Excel file that you have pasted in the screenshot.