Percentage of grand totals and cumulative totals on it.

[Anonymous]

Hi,

I have a requirement where in I need to calculate percentage of cost by grand totals.

I am able to do this by selecting the value properties and selecting show values as percent of grand totals, But the issues is i need to calculate the cumulative totals of this percentage value.

I am unable to figure it out how to create a measure which will calculate percentage of the grand totals and then how to calculate cumulative totals .

As we can see in the table I am unable to calculate Cumulative totals.

Can Anyone please help.

Unit	Item	Quantity	%GT Quantity	Cumulative Percent
12225	1123	5000	29.41%	29.41%
12225	2135	4600	27.05%	56.46%
58556	1254	3500	20.58%	77.05%
45896	1005	2000	11.76%	88.80%
45896	6589	1900	11.17%	99.97%
Total		17000	100%

Thanks 

Waseem

[parry2k]

@Anonymous what would be the sort order for cummulative total, is it unit?

[parry2k]

@Anonymous add following measures ,  change table and column name as per your data model and change the format for % Measure and % Cumm Total  to Percentage

On table visual, put Unit, Item and following measures and you will have the result.

Sum of Quantity = SUM ( 'Table (2)'[Quantity] ) 

% Measure = DIVIDE ( [Sum of Quantity], CALCULATE( [Sum of Quantity], ALL() ) ) 

% Cumm Total = CALCULATE( [% Measure], FILTER ( ALL ( 'Table (2)'[Unit], 'Table (2)'[Item]  ), 'Table (2)'[Unit] <= MAX ( 'Table (2)'[Unit] ) ) ) 

[Anonymous]

@parry2k , Thanks, It did worked.

In Cumulative totals I want to have totals, which should be sorted by Unit,Item, and Company filters,

I am trying with this DAX but it is not working,

% Cumulative Total = Calculate( [%Measure], Filter(Allselected( Table1(Item), Table1(Item)<=Max(Table1(Item)),Filter(Allselected( Table1(Branch), Table1(Branch)<=Max(Table1(Branch)),Filter(Allselected( Table1(Company), Table1(Company)<=Max(Table1(Company)))

Can you please help me with this.

Thanks

Waseem

[Ashish_Mathur]

Hi,

Share some data and show the expected result.

[Anonymous]

Hi,

As we can see in the .pbix file, I have Branch, Item, Sum Item Wise(Sum of quantity item wise sorted in descending order), Here I have calculated contribution measure which is the value to the percentage of Sum item wise to the grand totals, 

I want to have a cumulative totals of the Measure Contribution which should be dynamic, when i change filter of item and branch it should calcuate and display the cumulative totals.

I have created a measure as rolling contribution but it doesn't give the required results.

https://drive.google.com/file/d/1dxwmA-6bKscJiZZKWDGZwmTgErbDnqWA/view?usp=sharing

Thanks

Wasem

[Anonymous]

I want to have sum by item wise, But anyhow if I am able to do the cumulative of contribtution only on Quantity bases, in descending order , Where my item and business unit filter will work dynamially , That is fine.

Item	Business Unit	Quantity	Contribution	Cumulative
121179	25601004	100001	0.17634	0.17634
120899	25601004	100000	0.176339	0.352679
120952	25601004	100000	0.176339	0.529017
136388	25601004	96420	0.170026	0.699043
128052	25601004	76560	0.135005	0.834048
119344	25601004	27080	0.047752	0.8818
135272	25601004	13506	0.023816	0.905617
118462	25601004	10000	0.017634	0.92325
118463	25601004	8890	0.015676	0.938927
120889	25601004	5858	0.01033	0.949257
120867	25601004	4291	0.007567	0.956824
9444	25601004	2400	0.004232	0.961056
121575	25601004	2400	0.004232	0.965288

Thanks

[Ashish_Mathur]

Hi,

Based on this table that you have pasted, please show the exact expected result.

[Ashish_Mathur]

Hi,

Why have you created a Table 2 calculated table?  Why not build your visual from Table1 itself?  In Table1, what is the purpose of creating "Sum Itme Wise" column?

[v-shex-msft]

Hi @Anonymous,

AFAIK, calculate function use 'AND' logic to link all filters. Your formula can only get the wrong result for multiple range filter effects.

For your requirement, I'd like to suggest you take a look at the following link to add a rank field of multiple columns based on ISONORAFTER function, then you can use this field as the index to calculate the rolling total.

Sorting by multiple columns 

Regards,

Xiaoxin Sheng

[parry2k]

@Anonymous may be concatenate these 3 fields and then use that field for your cumulative total.  If still doesn't work, share the pbix file and remove sensitive info befoe sharing.

Would appreciate Kudos 🙂 if my solution helped.

[Anonymous]

@parry2k , Thanks for your quick response, 

I want to see % measure chaning as per item, unit and I have company column as well, Measure is working fine when I see it in table visual withput any filters, but when i try to put a slicer of unit, item and company it is not working dynamically.

Thanks.

waseem

[parry2k]

@Anonymous instead of ALL user ALLSELECTED

Would appreciate Kudos 🙂 if my solution helped.