cancel
Showing results for
Did you mean:
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

12 REPLIES 12
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

Regular Visitor

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

Super User

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

Regular Visitor

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):

Eva

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

Regular Visitor

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

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

Regular Visitor

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

Super User

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

Regular Visitor

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".

Super User

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 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

Regular Visitor

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

Announcements

#### The Power BI Community Show

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

#### Charticulator Design Challenge

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

#### Check it out!

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