topic Re: Calculate the ranking and cumulative contribution in calculated columns in DAX Commands and Tips
https://community.powerbi.com/t5/DAX-Commands-and-Tips/Calculate-the-ranking-and-cumulative-contribution-in-calculated/m-p/1903500#M41066
<P><LI-USER uid="239341"></LI-USER>, I want to create calculated columns, because I want after that to apply some filter scenarios, and for me is easier to handle. If you could calculate the cumulative materials contribution based on ranking (column f) you would help me a lot.<BR />thanx</P><P>Kostas</P>Wed, 16 Jun 2021 09:55:28 GMTkostask2021-06-16T09:55:28ZCalculate the ranking and cumulative contribution in calculated columns
https://community.powerbi.com/t5/DAX-Commands-and-Tips/Calculate-the-ranking-and-cumulative-contribution-in-calculated/m-p/1901431#M41006
<P>Hi guys!</P><P> </P><P>I'm trying to figure out the following problem, by creating <STRONG><U>calculated columns</U></STRONG> on my original table, and not measures.</P><P> </P><P><span class="lia-inline-image-display-wrapper lia-image-align-left" image-alt="a.PNG" style="width: 593px;"><img src="https://community.powerbi.com/t5/image/serverpage/image-id/534054i4FFD5D1D6ED5A2B6/image-size/large?v=v2&px=999" role="button" title="a.PNG" alt="a.PNG" /></span></P><P> </P><P> </P><P> </P><P> </P><P> </P><P> </P><P> </P><P> </P><P>So I have many rows with sold materials. Each row contains material and sales amount (B).</P><P>I created the total sales by material (C) which came to every row.</P><P>I also created the material's contribution to total sales (D) for every row also.</P><P> </P><P>What I miss, is to create the ranking of every material based on its contribution (Column E) and finally (I guess the most tricky) the cumulative contribution of every material, based on its ranking (1st, 1st+2nd, 1st+2nd+3rd, etc..)</P><P> </P><P>Any help would be appreciated</P><P> </P><P>Thanx</P><P>Kostas</P>Tue, 15 Jun 2021 13:01:47 GMThttps://community.powerbi.com/t5/DAX-Commands-and-Tips/Calculate-the-ranking-and-cumulative-contribution-in-calculated/m-p/1901431#M41006kostask2021-06-15T13:01:47ZRe: Calculate the ranking and cumulative contribution in calculated columns
https://community.powerbi.com/t5/DAX-Commands-and-Tips/Calculate-the-ranking-and-cumulative-contribution-in-calculated/m-p/1903433#M41061
<P>Actually, the remaining calculations are not too difficult but why don't you calculate all of this in Power Query?</P>Wed, 16 Jun 2021 09:31:13 GMThttps://community.powerbi.com/t5/DAX-Commands-and-Tips/Calculate-the-ranking-and-cumulative-contribution-in-calculated/m-p/1903433#M41061daxer2021-06-16T09:31:13ZRe: Calculate the ranking and cumulative contribution in calculated columns
https://community.powerbi.com/t5/DAX-Commands-and-Tips/Calculate-the-ranking-and-cumulative-contribution-in-calculated/m-p/1903457#M41063
<P>Hi <LI-USER uid="210525"></LI-USER> .</P><P>To calculate Ranking of material you can use,<BR />Rank=RANKX(table,table[Material Contribution]).<BR /> </P><P> </P>Wed, 16 Jun 2021 09:40:31 GMThttps://community.powerbi.com/t5/DAX-Commands-and-Tips/Calculate-the-ranking-and-cumulative-contribution-in-calculated/m-p/1903457#M41063SanketBhagwat2021-06-16T09:40:31ZRe: Calculate the ranking and cumulative contribution in calculated columns
https://community.powerbi.com/t5/DAX-Commands-and-Tips/Calculate-the-ranking-and-cumulative-contribution-in-calculated/m-p/1903500#M41066
<P><LI-USER uid="239341"></LI-USER>, I want to create calculated columns, because I want after that to apply some filter scenarios, and for me is easier to handle. If you could calculate the cumulative materials contribution based on ranking (column f) you would help me a lot.<BR />thanx</P><P>Kostas</P>Wed, 16 Jun 2021 09:55:28 GMThttps://community.powerbi.com/t5/DAX-Commands-and-Tips/Calculate-the-ranking-and-cumulative-contribution-in-calculated/m-p/1903500#M41066kostask2021-06-16T09:55:28ZRe: Calculate the ranking and cumulative contribution in calculated columns
https://community.powerbi.com/t5/DAX-Commands-and-Tips/Calculate-the-ranking-and-cumulative-contribution-in-calculated/m-p/1903532#M41069
<P><LI-USER uid="210525"></LI-USER> </P><P> </P><P>Calculated columns, especially in fact tables, should be avoided at all costs because such columns do not participate in the data compressing phase and thus they tables are not optimally compressed, hence DAX queries will not run at the maximum speed possible. This rule is especially important in models with fact tables that contain millions and millions of rows. Columns should always be calculated either in the source system and then brought over into Power BI or they should be calculated in Power Query (that's what this tool is for). Surely it's not hard at all to do all your calculations in Power Query. I could create the code for you but not right now as I'm at work <span class="lia-unicode-emoji" title=":slightly_smiling_face:">ðŸ™‚</span> If you wait a bit, I'll give you M code that you'll be able to use in Power Query on your data. Up to you.</P>Wed, 16 Jun 2021 10:07:08 GMThttps://community.powerbi.com/t5/DAX-Commands-and-Tips/Calculate-the-ranking-and-cumulative-contribution-in-calculated/m-p/1903532#M41069daxer2021-06-16T10:07:08ZRe: Calculate the ranking and cumulative contribution in calculated columns
https://community.powerbi.com/t5/DAX-Commands-and-Tips/Calculate-the-ranking-and-cumulative-contribution-in-calculated/m-p/1903663#M41076
<P>Hello <LI-USER uid="210525"></LI-USER> <BR />You can try this formula.<BR />Cumulative materials =<BR />VAR CurrentRank = 'Sales'[Material Ranking]<BR />RETURN<BR />CALCULATE (<BR />SUM ( Material Contribution ),<BR />FILTER ( Sales, 'Sales'[Material Ranking] <= CURRENTRANK )<BR />)<BR /><BR /><A href="https://community.powerbi.com/t5/DAX-Commands-and-Tips/How-to-Get-Your-Question-Answered-Quickly/m-p/1626726#M32906" target="_self">How to get your question answered quickly</A> </P>Wed, 16 Jun 2021 11:09:14 GMThttps://community.powerbi.com/t5/DAX-Commands-and-Tips/Calculate-the-ranking-and-cumulative-contribution-in-calculated/m-p/1903663#M41076TarunSharma2021-06-16T11:09:14Z