cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper I
Helper I

how can i calculate using a single measure on different rows

hi all

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.

 

e.g.

here is my standing data. L1 incorporates L2 data

L1 10

L2 6

 

what i need to output is this

L1 4

L2 6

 

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

 

Dataset

CompanyProfit
A600.00
B300.00
C200.00

 

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.

 

CompanyProfit
A400.00
B300.00
C200.00

 

 

hope this is clearer, should have spent the time doing this before,

 

 

thanks

glen

10 REPLIES 10
Advocate I
Advocate I

I need more detailed information like sample data in original table and your desired output in visual so that I can try to test.

Official Website: Situs Domino

situs domino online.PNG

Microsoft
Microsoft

Hi @glenmclaren,

 

I need more detailed information like sample data in original table and your desired output in visual so that I can try to test.

 

Regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Resolver I
Resolver I

Hello,

 

I am not sure that this is really what you need,

Here is a way to proceed with the data your provided :

007.png

 

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

008.png

 

009.png

 

If this is not exactly what you want, please provide an example with more data.

hi, its not quite, i have posted another example, thanks

 

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.

 

 

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

Ok, Let's take your table :

 

CompanyProfit
A400.00
B300.00
C200.00

 

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 ?

 

How can you be sure that the company B amount is not included in company A ?

We know that all company B profits are only company B

thanks

glen

 

How can you be sure that the company B amount is not included in company A ?

we know all Company B profits are only for Company B.

 

I really do not understand how you manage to know that the amount of the company C is included in company A.

 

Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Wave Release 2

Check out the updates in Power BI.

Overview of Power BI 2020 release wave 2!

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors