Calculation change with drill down

[analytics_ISFC]

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 No	State	BranchName	POS
1	Rajasthan	Jaipur	2
2	Maharashtra	Nasik	5
3	MP	Indore	1
4	Rajasthan	Jaipur	7
5	Maharashtra	Nasik	3
6	Rajasthan	Jaipur	1
7	Rajasthan	jaiselmer	3
8	Maharashtra	nagpur	1
9	MP	Indore	2
10	MP	bhopal	2

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. 

[analytics_ISFC]

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]

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

Best
D

[analytics_ISFC]

yes.

Measure is throwing a different result altogether.

[Anonymous]

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

Best
D

[analytics_ISFC]

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]

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

Best

D

[analytics_ISFC]

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]

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:

 

Is this what you wanted?

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

Best

D

[analytics_ISFC]

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]

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

Best
D

[analytics_ISFC]

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]

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

[analytics_ISFC]

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

[Anonymous]

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

[analytics_ISFC]

Okay, thanks for the help