Forum Discussion
Cross Level Calculation- Power BI
We are looking for a solution for one of the requirements in Power BI Report.
Requirement:
We need to display the value from the highest level (L1) to the lowest level (L4) in a card visual, following a specific hierarchy.
We have four levels: L1, L2, L3, and L4, with L1 being the topmost and L4 the bottommost. Multiple items can be tagged at each level.
If any item is tagged at L1, the value at L1 should be displayed. If no items are tagged at L1, we move to L2, and if items are tagged at L2,
we display the value at L2. This process continues down to L4.
Note- Value is available at L4 level only and it has been summarized for higher levels to display in report.
Key conditions:
- Multiple items can be tagged to any level, but we must avoid duplication of values as the value for each node remains the same.
- If a level L3 contains multiple L4 items, the values of all L4 items are summed and displayed. The same aggregation rule applies to other levels as well.
For Example: Please check below snap, colour represents levels, number indicates value. Orange boarder shapes represents item tagged to it.
Here we want Value = T01+T011+Bnglr (10+30+40=80) when user opens reports. As we have L1 ,L2,L3,L4 filters as well for user to slice & dice.
When user selects L3 slicer for Hyderabad the Value should be 40 and not 60.(which is addition of its tagged children only)
2 Replies
Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information or anything not related to the issue or question.
If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
Please show the expected outcome based on the sample data you provided.
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523Hi lbendlin ,
Thanks for the quick response.
Hi Nausee ,
Please have a try.
DynamicValue = VAR IsL1Filtered = ISFILTERED('Table'[L1]) VAR IsL2Filtered = ISFILTERED('Table'[L2]) VAR IsL3Filtered = ISFILTERED('Table'[L3]) VAR IsL4Filtered = ISFILTERED('Table'[L4]) RETURN IF(IsL1Filtered, SUM('Table'[ValueL1]), IF(IsL2Filtered, SUM('Table'[ValueL2]), IF(IsL3Filtered, SUM('Table'[ValueL3]), SUM('Table'[ValueL4]) ) ) )How to Get Your Question Answered Quickly - Microsoft Fabric Community
If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .
Best Regards
Community Support Team _ RongtieIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
