topic Re: how can i calculate using a single measure on different rows in Desktop
https://community.powerbi.com/t5/Desktop/how-can-i-calculate-using-a-single-measure-on-different-rows/m-p/384742#M175153
<P>hi, its not quite, i have posted another example, thanks</P>Tue, 27 Mar 2018 14:04:39 GMTglenmclaren2018-03-27T14:04:39Zhow can i calculate using a single measure on different rows
https://community.powerbi.com/t5/Desktop/how-can-i-calculate-using-a-single-measure-on-different-rows/m-p/383869#M174742
<P>hi all</P><P>subject sounds bit compliated, but in reality, hoping this should not be.</P><P> </P><P>i have a requirement where i wish to calculate a new measure from a single measure over different rows.</P><P>it stems from a hierarchy problem.</P><P> </P><P>one level of the hierarchy, incorporates figures from other levels. each row is a level of its own, so i have to deduct the child level from the parent level.</P><P> </P><P>e.g.</P><P>here is my standing data. L1 incorporates L2 data</P><P>L1 10</P><P>L2 6</P><P> </P><P>what i need to output is this</P><P>L1 4</P><P>L2 6</P><P> </P><P>i am hoping this can be done via some form of set function, and not have to create xcross tabs or extra tables.</P><P> </P><P>here is another example to help make things clearer</P><P> </P><P>Dataset</P><TABLE><TBODY><TR><TD>Company</TD><TD>Profit</TD></TR><TR><TD>A</TD><TD>600.00</TD></TR><TR><TD>B</TD><TD>300.00</TD></TR><TR><TD>C</TD><TD>200.00</TD></TR></TBODY></TABLE><P> </P><P>Above, Company C is rolled up into Company A (600), and Company A has its own profits (400).</P><P>I want to report all Company profits on their own, so somehow i need to interrogate the dataset when working out Company A's profit, to deduct the 200 from 600 and report 400. So the report should look like this.</P><P> </P><TABLE><TBODY><TR><TD>Company</TD><TD>Profit</TD></TR><TR><TD>A</TD><TD>400.00</TD></TR><TR><TD>B</TD><TD>300.00</TD></TR><TR><TD>C</TD><TD>200.00</TD></TR></TBODY></TABLE><P> </P><P> </P><P>hope this is clearer, should have spent the time doing this before,</P><P> </P><P> </P><P>thanks</P><P>glen</P>Tue, 27 Mar 2018 07:20:40 GMThttps://community.powerbi.com/t5/Desktop/how-can-i-calculate-using-a-single-measure-on-different-rows/m-p/383869#M174742glenmclaren2018-03-27T07:20:40ZRe: how can i calculate using a single measure on different rows
https://community.powerbi.com/t5/Desktop/how-can-i-calculate-using-a-single-measure-on-different-rows/m-p/384366#M174985
<P>Hello,</P><P> </P><P>I am not sure that this is really what you need,</P><P>Here is a way to proceed with the data your provided :</P><P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="007.png" style="width: 230px;"><img src="https://community.powerbi.com/t5/image/serverpage/image-id/88261i3E3C2C2678BEDE00/image-size/large?v=1.0&px=999" role="button" title="007.png" alt="007.png" /></span></P><P> </P><PRE>Result =
IF(MAX(Data3[Level])="L1";SUM(Data3[Data]);0)+
IF(MAX(Data3[Level])="L1";-1;1)*
CALCULATE(SUM(Data3[Data]);FILTER(ALL(Data3);Data3[Level]="L2"))</PRE><P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="008.png" style="width: 150px;"><img src="https://community.powerbi.com/t5/image/serverpage/image-id/88262i3E0085F376E01AC9/image-size/large?v=1.0&px=999" role="button" title="008.png" alt="008.png" /></span></P><P> </P><P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="009.png" style="width: 152px;"><img src="https://community.powerbi.com/t5/image/serverpage/image-id/88263i886D66BF3251D051/image-size/large?v=1.0&px=999" role="button" title="009.png" alt="009.png" /></span></P><P> </P><P>If this is not exactly what you want, please provide an example with more data.</P>Tue, 27 Mar 2018 06:50:26 GMThttps://community.powerbi.com/t5/Desktop/how-can-i-calculate-using-a-single-measure-on-different-rows/m-p/384366#M174985Salonic2018-03-27T06:50:26ZRe: how can i calculate using a single measure on different rows
https://community.powerbi.com/t5/Desktop/how-can-i-calculate-using-a-single-measure-on-different-rows/m-p/384380#M174992
<P>Hi <LI-USER uid="58286"></LI-USER>,</P>
<P> </P>
<P>I need more detailed information like sample data in original table and your desired output in visual so that I can try to test.</P>
<P> </P>
<P>Regards,</P>
<P>Yuliana Gu</P>Tue, 27 Mar 2018 07:09:03 GMThttps://community.powerbi.com/t5/Desktop/how-can-i-calculate-using-a-single-measure-on-different-rows/m-p/384380#M174992v-yulgu-msft2018-03-27T07:09:03ZRe: how can i calculate using a single measure on different rows
https://community.powerbi.com/t5/Desktop/how-can-i-calculate-using-a-single-measure-on-different-rows/m-p/384742#M175153
<P>hi, its not quite, i have posted another example, thanks</P>Tue, 27 Mar 2018 14:04:39 GMThttps://community.powerbi.com/t5/Desktop/how-can-i-calculate-using-a-single-measure-on-different-rows/m-p/384742#M175153glenmclaren2018-03-27T14:04:39ZRe: how can i calculate using a single measure on different rows
https://community.powerbi.com/t5/Desktop/how-can-i-calculate-using-a-single-measure-on-different-rows/m-p/385856#M175668
<P> </P><P>Something I don't understand is how is stored this information :</P><P> </P><P>"<SPAN>Company C is rolled up into Company A</SPAN>"</P><P> </P><P>Is it another table ?</P><P> </P><P>If so could you give an example with all the table to use in the dataset.</P><P> </P><P> </P>Wed, 28 Mar 2018 19:28:16 GMThttps://community.powerbi.com/t5/Desktop/how-can-i-calculate-using-a-single-measure-on-different-rows/m-p/385856#M175668Salonic2018-03-28T19:28:16ZRe: how can i calculate using a single measure on different rows
https://community.powerbi.com/t5/Desktop/how-can-i-calculate-using-a-single-measure-on-different-rows/m-p/386063#M175759
Hi. Its not another table. There is only one dataset. See my second example above. Also my desired output. 'Rolled up' means company C amount is included in company A. I need to exclude compay C data fro.lm company A.<BR />ThanksThu, 29 Mar 2018 05:38:38 GMThttps://community.powerbi.com/t5/Desktop/how-can-i-calculate-using-a-single-measure-on-different-rows/m-p/386063#M175759glenmclaren2018-03-29T05:38:38ZRe: how can i calculate using a single measure on different rows
https://community.powerbi.com/t5/Desktop/how-can-i-calculate-using-a-single-measure-on-different-rows/m-p/386306#M175878
<P>Ok, Let's take your table :</P><P> </P><TABLE><TBODY><TR><TD>Company</TD><TD>Profit</TD></TR><TR><TD>A</TD><TD>400.00</TD></TR><TR><TD>B</TD><TD>300.00</TD></TR><TR><TD>C</TD><TD>200.00</TD></TR></TBODY></TABLE><P> </P><P>You are writing : "<SPAN>company C amount is included in company A</SPAN>"</P><P>How can you be sure that the company B amount is not included in company A ?</P><P> </P>Thu, 29 Mar 2018 09:58:03 GMThttps://community.powerbi.com/t5/Desktop/how-can-i-calculate-using-a-single-measure-on-different-rows/m-p/386306#M175878Salonic2018-03-29T09:58:03ZRe: how can i calculate using a single measure on different rows
https://community.powerbi.com/t5/Desktop/how-can-i-calculate-using-a-single-measure-on-different-rows/m-p/386338#M175886
<P><SPAN>How can you be sure that the company B amount is not included in company A ?</SPAN></P><P><SPAN>We know that all company B profits are only company B</SPAN></P><P><SPAN>thanks</SPAN></P><P><SPAN>glen</SPAN></P><P> </P>Thu, 29 Mar 2018 10:28:32 GMThttps://community.powerbi.com/t5/Desktop/how-can-i-calculate-using-a-single-measure-on-different-rows/m-p/386338#M175886glenmclaren2018-03-29T10:28:32ZRe: how can i calculate using a single measure on different rows
https://community.powerbi.com/t5/Desktop/how-can-i-calculate-using-a-single-measure-on-different-rows/m-p/386339#M175887
<P><SPAN>How can you be sure that the company B amount is not included in company A ?</SPAN></P><P><SPAN>we know all Company B profits are only for Company B.</SPAN></P>Thu, 29 Mar 2018 10:29:16 GMThttps://community.powerbi.com/t5/Desktop/how-can-i-calculate-using-a-single-measure-on-different-rows/m-p/386339#M175887glenmclaren2018-03-29T10:29:16ZRe: how can i calculate using a single measure on different rows
https://community.powerbi.com/t5/Desktop/how-can-i-calculate-using-a-single-measure-on-different-rows/m-p/386477#M175946
<P> </P><P>I really do not understand how you manage to know that the amount of the company C is included in company A.</P><P> </P>Thu, 29 Mar 2018 14:28:34 GMThttps://community.powerbi.com/t5/Desktop/how-can-i-calculate-using-a-single-measure-on-different-rows/m-p/386477#M175946Salonic2018-03-29T14:28:34ZRe: how can i calculate using a single measure on different rows
https://community.powerbi.com/t5/Desktop/how-can-i-calculate-using-a-single-measure-on-different-rows/m-p/391555#M178481
<P><SPAN>I need more detailed information like sample data in original table and your desired output in visual so that I can try to test.</SPAN></P><P><SPAN><STRONG>Official Website</STRONG>: <A href="http://situsdomino.co" target="_self">Situs Domino</A></SPAN></P><P><SPAN><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="situs domino online.PNG" style="width: 501px;"><img src="https://community.powerbi.com/t5/image/serverpage/image-id/89965i1F962678ECC84BAC/image-size/large?v=1.0&px=999" role="button" title="situs domino online.PNG" alt="situs domino online.PNG" /></span></SPAN></P>Sat, 07 Apr 2018 06:38:34 GMThttps://community.powerbi.com/t5/Desktop/how-can-i-calculate-using-a-single-measure-on-different-rows/m-p/391555#M178481situsdomino2018-04-07T06:38:34Z