Using IF Function over Net Subtotals

[DLJ]

Hi all,

I would be very grateful if somebody can help me.

I have an issue when using function IF as follows:

'EQUITY AJUSTED = (SUMX(Global_Table;IF([Measure_1] > 0;

([Measure_2]*RELATED('Specific_Table'[Column_1]))+[Measure_1];

([Measure_2]*RELATED('Specific_Table'[Column_1])

My problem is the following:

I want to use the IF function ([Measure_1] > 0) over the subtotal (Measure_1) of the first level of the hierarchy, because such function IF has sense using it over the net subtotal, but not over the detailed accounts if I drill down in the matrix.

If I drilled down expanding the table (second level of the hierarchy), I can find several rows below with different figures (Measure_1) which may be higher or lower than 0. With my formula, I can see that my IF function is operating and calculating the result differently row by row (but It has no sense if it is not the net subtotal).

What is what I want to get?

If the net subtotal (Measure_1) is > 0; do for all rows ([Measure_2]*RELATED('Specific_Table'[Column_1])+[Measure_1])

Else; do for all rows ([Measure_2]*RELATED('Specific_Table'[Column_1])

Is it possible? Any alternative solution?

Thank you so much in advanced.

Regards

[v-chuncz-msft · Accepted solution]

@DLJ 

You may check if the posts below help.

• https://community.powerbi.com/t5/Desktop/Removing-nulls-from-lowest-level-in-Drill-down-hierarchy/m-...
• https://community.powerbi.com/t5/Desktop/Hide-matrix-rows-without-affecting-values/m-p/646134#M30968...

[amitchandak]

I think you have to use isfiltered or isinscope

refer :https://powerpivotpro.com/2013/03/hasonevalue-vs-isfiltered-vs-hasonefilter/

[DLJ]

Thank you for the tip!

Using ISINSCOPE function, I think I am closer to the solution (thanks!), but I do not having yet.

This is is the progress:

I have identified with "TRUE" the level of the hierarchy in which I want to use the IF function.

ISINSCOPE PROMOCION LEVEL IS TRUE = IF(ISINSCOPE(dimPARTIDAS[APARTADO PRINEX]);FALSE();TRUE())

So I would like to use IF function over subtotals in such level. E.g:

EQUITY ISINSCOPE = IF([ISINSCOPE PROMOCION LEVEL IS TRUE]=TRUE(); ...

But I don't have the right calculate.

In my current formula I am using also SUMX and that is the problem I think (because I guess this is the reason why the result I am getting is a Total Sum of the operations (IF function) row by row. I just need the calculate in my level chosen over the net subtotals (not row by row).

Maybe, a solution would to calculate separately the net results of each measure for the "TRUE" level and afterwards do the IF function.

Thanks again

[DLJ]

By the way, my current formula would be:

EQUITY ISINSCOPE = IF([ISINSCOPE PROMOCION LEVEL IS TRUE]=TRUE();

CALCULATE(SUMX(fctPPTOS;IF([EQUITY NECESARIO % GASTOS FINANCIABLES]>0;...;...)))

Thanks!