Invert signs on calculated measure

[rnambiar]

I have a Profit & Loss report that I have created comparing 2 years of information

The Header table is as follows:

The Subheader Table is as follows:

The Header and Subheader table is linked to the fact table

I am trying to calculate the percentage difference CY and PY.  When I write a measure  as follows:

%Diff = DIVIDE([CY]-[PY],[PY])    ----- it works fine except that that I need to have the signs inverted for Expenses.

SO I tried this and this work only if I have one level in the table.

CYvsLY2 = IF(SELECTEDVALUE(Header[Invert])=1, DIVIDE([CY]-[PY],[PY])*-1, DIVIDE([CY]-[PY],[PY]))

However, when I have multiple levels in the table as in the first figure, I tried the following and got an error:

CYvsLY%1 =

VAR isheaderfiltered = ISFILTERED(Header[Header])

VAR isSubheaderfiltered = ISFILTERED(Subheader[Subheader])

VAR iseitherfiltered = OR(isSubheaderfiltered,isheaderfiltered)

VAR Delta = IF(SELECTEDVALUE(Header[Invert])=1, DIVIDE([CY]-[PY],[PY])*-1, DIVIDE([CY]-[PY],[PY]))

VAR Result = IF(iseitherfiltered,Delta,BLANK())

Return Result

Any thoughts on how to make this work would be appreciated?

[v-jtian-msft · Accepted solution]

Hi,@rnambiar,I am glad to help you.
I tried to reproduce your problem:Correctly calculating [CYvsLY2] when applying multiple levels of filtering in a matrix
Unfortunately, I don't seem to reproduce your problem, which I think may be caused by the different structure of the model data and the different computational environments.
[CYvsLY%1] is able to correctly compute the results of [CYvsLY2] at the single-level level
In fact I am more interested in the calculation process of your [CY] and [PY] measures because their calculation environment and results will directly affect [CYvsLY%1], but unfortunately, according to the screenshots you provided, I can't find any incorrectly displayed results for [CYvsLY%1].
Can you explain in detail about your model data and the corresponding relationships between the tables and show in full the MEASURE of what you think went wrong, i.e. show the end result of [CYvsLY%1] in the first screenshot you have given, I don't see it in your screenshot.
So I can't confirm exactly what your problem is referring to and whether the [CYvsLY%1] MEASURE is miscalculated or not displaying properly.

Here is my test data and test results

I think you need to pay attention to two things, the first is whether the [CY] and [PY] measures are calculated in a context that matches the smallest hierarchical field in the matrix that you placed, and I'm a little bit concerned about the final hierarchical display of [4014] and [4015], which you didn't talk about in your description.

The second point is to make sure that your hierarchical filtering criteria are correct for
This part:

VAR isheaderfiltered = ISFILTERED(Header[Header])
VAR isSubheaderfiltered = ISFILTERED(Subheader[Subheader])
VAR iseitherfiltered = 
OR(isSubheaderfiltered, isheaderfiltered)

Can you elaborate on the logic by which you are performing this level of filtering?

Can you answer my query in detail, please provide the pbix file which does not contain sensitive data, it will be helpful to solve your problem.

https://nam06.safelinks.protection.outlook.com/?url=https%3A%2F%2Fcommunity.powerbi.com%2Ft5%2FCommu...

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Carson Jian,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[rnambiar]

Hey v-jtian-msft,

Thank you for the detailed reply.  The error I am getting is as follows when I add the [CYvsLY%1] measure to the table.

However, I increased the memory in the cache and that fixed the problem.

Spoiler

Spoiler