cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
frisbina2002
Regular Visitor

How to get all PnL Lines indexed by GR

Hi, I would like to see each PnL line calculated as a % of GR for instance: Cosumer Activation Variable / GR LY = 6,3%. I created  a measure GR LY as the denominator but if i want to divide it by each PnL line (measure GR index LY) it automatically divides only Gross Revenue Line. Anybody knows what formula would fit better? Thanks. Eva

 

frisbina2002_1-1615534836187.png

 

 

12 REPLIES 12
selimovd
Super User
Super User

Hey Eva,

 

yes, the issue can be in a few places.

Can you tell me the formula of [GR LY]? Or could you provide an anonymized version of the file? Then I can check why the measure is not showing the desired values.

 

Best regards

Denis

Dear @selimovd , unfortunately the dashboard conists of confidencial data so i rather not to share it by any mean. Below is formula of GR LY if helps. I appreciate your support. Eva

 

frisbina2002_0-1615559237729.png

 

Hey Eva,

 

maybe you realized that is exactly the formula I used for the [Gross Revenue GR LY] 😉 

Can you in this case paste the code for the measure [LY]?

 

Best regards

Denis

 Beginners, what to say 😄 So the LY is taken from my data table that includes a column for PnL lines (BW PnL) and a column for $$$ (LY DATA):

frisbina2002_0-1615561989815.pngfrisbina2002_1-1615562052355.png

 

Eva

selimovd
Super User
Super User

Hey @frisbina2002 ,

 

with the following measure you will always get the value [GR LY] from Gross Revenue:

Gross Revenue GR LY = CALCULATE( [GR LY], myTable[PnL 1] = "Gross Revenue" )

 

And then you can divide the [LY] by [Gross Revenue LY]

 

If you need any help please let me know.
If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍 

Best regards
Denis

Blog: WhatTheFact.bi

Dear @selimovd . Unfortunately this is not yet working. The formula above will show GR only at line Gross Revenue hence the rest of rows wil remian zero %. 😞 Eva

frisbina2002_0-1615557338428.png

 

selimovd
Super User
Super User

Hello @frisbina2002 ,

 

try the following.

You can calculate the Total with the CALCULATE function:

Total PnL = CALCULATE([LY], REMOVEFILTERS(myTable[PnL 1])

 

Then you can divide the [LY] by the total.

 

If you need any help please let me know.
If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards
Denis

Blog: WhatTheFact.bi

Hello @selimovd , I may not be clear in the problem. My issue is not the total line (i will remove it later anyway) but it is the column "GR LY Index" which shows zero % in each line with exception of GR. I would like to see how each PnL  row stands agains GR as a % ratio. I could create a measure of each PnL Line saying: "Consumer Activation Variable" divided by "GR" but I would like to avoid this manual solution of possible. Eva

Dear Eva,

 

it seems obvious but the [GR Index LY] is empty because the values for [GR LY] are empty in the other rows.

So from a mathematical point of view if it's empty then 0% should be correct. If you want to see a different behavior you have to change the formula of [GR LY]. Do you want to compare against the total of [GR LY] or how should the values of [GR LY] be in the other rows?

 

If you need any help please let me know.
If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards
Denis

Blog: WhatTheFact.bi

Dear @selimovd 

Got it. So we need to get in each row same value of GR, do you know how? The final outcome will be Column "LY" and "GR Index LY" - see pic below. The GR LY measure is only a supportive measure to calculated "GR Index LY". 

 

frisbina2002_0-1615537331588.png

 

Dear @frisbina2002 ,

 

yes, I agree that [GR LY] is a supportive measure.

It's good that you are aware of the final outcome. Now you have to know how [GR LY] should look like.

 

If you need any help please let me know. But then you have to give me more information about [GR LY].
If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 


Best regards
Denis

Blog: WhatTheFact.bi

Dear @selimovd , GR measure will be always equal to " Gross Revenue" PnL line so in this case 70.7. So i will need a measure that allocates to any PnL line a value of "Gross Revenue". Thank you. Eva

Helpful resources

Announcements
May 23 2022 epsiode 5 without aka link.jpg

The Power BI Community Show

Welcome to the Power BI Community Show! Jeroen ter Heerdt talks about the importance of Data Modeling.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

Power BI Dev Camp Session 22 with aka link 768x460.jpg

Check it out!

Mark your calendars and join us on Thursday, May 26 at 11a PDT for a great session with Ted Pattison!

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!