cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
analytics_ISFC
Frequent Visitor

Calculation change with drill down

Hi Community,

 

I have to calculate percentage of principal outstanding at state and branch level but my denominator will change for State and Branch.
My dax is not behaving correctly for State as I drill up. it is keeping denominator of branchname.

 

For example:

 
Loan NoStateBranchNamePOS
1RajasthanJaipur2
2MaharashtraNasik5
3MPIndore1
4RajasthanJaipur7
5MaharashtraNasik3
6RajasthanJaipur1
7Rajasthanjaiselmer3
8Maharashtranagpur1
9MPIndore2
10MPbhopal2

 

My DAX is:

IF(HASONEVALUE('Summarized MIS'[State]),'Summarized MIS'[POS]/CALCULATE(SUM('Summarized MIS'[POS]), ALLEXCEPT('Summarized MIS','Summarized MIS'[State])),'Summarized MIS'[POS]/CALCULATE(SUM('Summarized MIS'[POS]), ALLEXCEPT('Summarized MIS','Summarized MIS'[BranchName])))
 
it is working for BranchName but not for State.
the value should change dynamically as I drill up or drill down. 
15 REPLIES 15
analytics_ISFC
Frequent Visitor

I have been stuck at this problem for a very long time and it is highly important for me to find a solution. Any help would be appreciated.

 

Thanks in advance!

Anonymous
Not applicable

This does not look to be a measure. It's a calculated column, right?

Best
D

yes.

Measure is throwing a different result altogether.

Anonymous
Not applicable

OK... So, why don't you calculate this in Power Query? This is, in fact, the place to do this.

Best
D

State and Branch are reference columns and you cant see them in Power Query. 

I dont know how this can be written in Power query. 

Is there a way you can help in write a calculated column or calculated measure that would help me achieve my objective?

Anonymous
Not applicable

With hierarchies, it's always tricky in Power BI. I think this is what you were asking for... Check the file attached.

 

Best

D

the file is not opening 

 

getting this error:

 

Feedback Type:
Frown (Error)

Error Message:
Object reference not set to an instance of an object.

 

Anonymous
Not applicable

It does open on my side without a problem. I just click the file, it gets downloaded and it opens in PBI Desktop without a glitch.

 

Here's what's in the file:

 State and Branch - Loan Parent %.PNG

Is this what you wanted?

 

I attach the file again but the first link is working correctly as well.

 

Best

D

 

I used your solution but it showing 100% for all the scenarios.

When i apply any filter, the value should change, but this is not happening.

Anonymous
Not applicable

Well, the calculation is relative to what's been selected. If you want an absolute calculation, you have to change the measure.

Best
D

That is where i am stuck. i dont know how to proceed with it. i want my calculations to change dynamically with applied filter.

Anonymous
Not applicable

Check out this final version. It has 2 measures. One that's relative to the selection in the slicer---% Parent (rel)---and another one that's absolute---% Parent (abs). One of them is the one you want.

 

Best

D

For this solution to work, branch and state should be in different tables. But I have them in the same table. 

Anonymous
Not applicable

If you do have them in the same table, then you should change it. Please try to learn something about proper dimensional design:

https://www.youtube.com/watch?v=78d6mwR8GtA&t=1247s

Best
D

Okay, thanks for the help

Helpful resources

Announcements
MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Urdu Hindi D365 Bootcamp 768x460.png

Urdu Hindi D365 Bootcamp

Dont miss our very own April Dunnam’s The Developer Guide to the Galaxy! Find out what the Power Platform has to offer for the traditional developer.

Top Solution Authors