Showing results for 
Search instead for 
Did you mean: 
J_4923 Frequent Visitor
Frequent Visitor

Creating a hierarchy and summarize per condition

I'm new to the world of Power BI and need some guidance/assistance on how to resolve following.
Below is a sample of my table profit.

I have four different geo locations origin, destination, payer and owner each of them on country, area and region level. To this there are also different profit measures origin, destination, payer and owner.
What I would like to achieve is total profit per geo.
 Key 1 region ASI should have profitorigin + profitdestination + profitpayer (as totalprofit) and EUR profitOwner  (as totalprofit)
And so on..
I would also like be able to drill down from region to area to country on total profit.
Any inputs appreciated, thanks!

Moderator v-yuezhe-msft

Re: Creating a hierarchy and summarize per condition

Hi @J_4923,

I am not quite clear about that how you calculate profit. If you mean that for key 1, total profit for ASI OriginRegion is 523.856+523.856+130.964, and total profit for EUR OwnerRegion is 130.964. Then for key 8, total profit for AME OriginRegion is 94.8+94.8+23.7 , and total profit for IMEA OwnerRegion is 23.7. You would need to unpivot your tables as shown in the first screenshot,  then create a column using the DAX and you can create a table visual to shown result.

totalprofit = IF('Table5 (2)'[Attribute]="OriginRegion",'Table5 (2)'[ProfitOrigin]+'Table5 (2)'[ProfitDestination]+'Table5 (2)'[ProfitPayer], IF('Table5 (2)'[Attribute]="OwnerRegion",'Table5 (2)'[ProfitOwner],0))

Lydia Zhang

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

Helpful resources


How to Get Your Question Answered Quickly

Power BI Super User, Greg Deckler, explains

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Ideas
Users Online
Currently online: 107 members 1,688 guests
Please welcome our newest community members: