topic Calculation change with drill down in DAX Commands and Tips

Calculation change with drill down

analytics_ISFC — Fri, 27 Mar 2020 07:30:20 GMT

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.

Re: Calculation change with drill down

analytics_ISFC — Fri, 27 Mar 2020 10:34:45 GMT

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!

Re: Calculation change with drill down

Anonymous — Fri, 27 Mar 2020 11:09:47 GMT

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

Best
D

Re: Calculation change with drill down

analytics_ISFC — Fri, 27 Mar 2020 11:15:24 GMT

yes.

Measure is throwing a different result altogether.

Re: Calculation change with drill down

Anonymous — Fri, 27 Mar 2020 11:56:55 GMT

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

Best
D

Re: Calculation change with drill down

analytics_ISFC — Fri, 27 Mar 2020 12:59:45 GMT

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?

Re: Calculation change with drill down

Anonymous — Fri, 27 Mar 2020 17:17:58 GMT

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

Best

Re: Calculation change with drill down

analytics_ISFC — Fri, 27 Mar 2020 15:13:03 GMT

the file is not opening

getting this error:

Feedback Type:
Frown (Error)

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

Re: Calculation change with drill down

Anonymous — Fri, 27 Mar 2020 17:16:32 GMT

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

Re: Calculation change with drill down

analytics_ISFC — Fri, 27 Mar 2020 17:30:59 GMT

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.

Re: Calculation change with drill down

Anonymous — Fri, 27 Mar 2020 18:17:34 GMT

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

Best
D

Re: Calculation change with drill down

analytics_ISFC — Fri, 27 Mar 2020 18:22:01 GMT

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

Re: Calculation change with drill down

Anonymous — Fri, 27 Mar 2020 19:55:39 GMT

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

Re: Calculation change with drill down

analytics_ISFC — Sat, 28 Mar 2020 08:23:22 GMT

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

Re: Calculation change with drill down

Anonymous — Sat, 28 Mar 2020 09:08:03 GMT

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

Re: Calculation change with drill down

analytics_ISFC — Sat, 28 Mar 2020 10:43:10 GMT

Okay, thanks for the help