subject sounds bit compliated, but in reality, hoping this should not be.
i have a requirement where i wish to calculate a new measure from a single measure over different rows.
it stems from a hierarchy problem.
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.
here is my standing data. L1 incorporates L2 data
what i need to output is this
i am hoping this can be done via some form of set function, and not have to create xcross tabs or extra tables.
here is another example to help make things clearer
Above, Company C is rolled up into Company A (600), and Company A has its own profits (400).
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.
hope this is clearer, should have spent the time doing this before,
I need more detailed information like sample data in original table and your desired output in visual so that I can try to test.
I am not sure that this is really what you need,
Here is a way to proceed with the data your provided :
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"))
If this is not exactly what you want, please provide an example with more data.
Something I don't understand is how is stored this information :
"Company C is rolled up into Company A"
Is it another table ?
If so could you give an example with all the table to use in the dataset.
Ok, Let's take your table :
You are writing : "company C amount is included in company A"
How can you be sure that the company B amount is not included in company A ?
This is a must watch for a message from Power BI!
Click here to read more about the December 2020 Updates!
Click here to read the latest blog and learn more about contributing to the Power BI blog!
Mark your calendars and join us for our next Power BI Dev Camp!.