How To Calculate Percentages In A field parameters Form In Power BI

DAX DAX Calculations
1

This video https://youtu.be/wbIkcRBlOao shows the correct% share. Unfortunately, the dax function from the movie does not work when using the new field parameters option.

2

Hello,

Can you send out PBIX file where that does not work?

Generally speaking, it should be working with field parameters. Field parameters are referring to the original columns and ISINSCOPE() should catch that. It does for me

image
image1040Ă—677 70.6 KB

You can also make sure that in DAX expresion calculating field parameters tables
image

The columns in NAMEOF() is refering to the same table[column] that the test in your ISINSCOPE(column)
test in your measure
image

3

Hello @Harris,

Thank You for posting your query onto the Forum.

@piniusz - The problem here, is not with the “ISINSCOPE()” function.

It has already been highlighted into the video that what happens when the order of the context changes (please refer the video again especially, from 9:55 minutes mark).

These are the exact words - “Since DAX evaluates the inner most context first and then moves towards the outermost context. And therefore, in the case of hierarchies, our order of condition also needs to be arranged accordingly. So in this case, since Products are at a lower level they will go first as a condition and since territories are in a upper level they’ll go last.”

And hence, in the case of Field Parameters, when it’s used, there’s no consistent layer of hierarchy i.e., it changes the layer of hierarchy in any manner as per the selection made into the slicer which introduces context ambiguity.

For this type of scenario’s where evaluation of contexts takes place based on set nature of hierarchy, one cannot inject context ambiguity by introducing it in the form of Field Parameters.

Please refer the video again for better understanding of the evaluation of context.

Hoping this explanation clears the understanding of the concept showcased into the video.

Thanks and Warm Regards,
Harsh

4 Likes
4

Field parameters.pbix (1.4 MB)

5

Are you form Poland Harris? Cześć :slight_smile:

@Harsh it seems that we both did not quite get a right guess what was the issue :slight_smile:

I think I have fixed your measure

Share_% = 
VAR CurrentUnits = [AC]
VAR _ALL_Units_of_Product =
    CALCULATE ( [AC], ALLSELECTED ( Produkty[Nazwa Produktu] ) )
VAR _all_units_of_cathegory =
    CALCULATE ( [AC], ALLSELECTED ( Produkty[Podkategoria] ) )
VAR _all_units_region =
    CALCULATE ( [AC], ALLSELECTED ( Dzielnice[Region] ) )
VAR _ALL_Units_of_dzielnica =
    CALCULATE ( [AC], ALLSELECTED ( Sales ) )
VAR _denominator =
    SWITCH (
        TRUE (),
        ISINSCOPE ( Produkty[Nazwa Produktu] ), _ALL_Units_of_Product,
        ISINSCOPE ( Produkty[Podkategoria] ), _all_units_of_cathegory,
        ISINSCOPE ( Dzielnice[Region] ), _all_units_region,
        ISINSCOPE ( Dzielnice[Dzielnica Nazwa] ), _ALL_Units_of_dzielnica,
        _all_units_region
    )
VAR _Result =
    DIVIDE ( CurrentUnits, _denominator, 0 )
RETURN
    _Result

That work’s for me
image

The issue in your measure was, that in your hierarchy you were using several columns from one table and in allselected() you were targeting entire table. By adding a column for each level it works as intended.

I also has changed the order of switch. From you “Oś X” table, that is your field parameter table, “Dzielnica nazwa” is on top of hierarchy so I adjusted the measure that when you are looking at the table at that level, it is showing you % share for entire sales. To accomplish that, I used allsected(sales) in calculation context

2 Likes
6

Działa! It works! thank you very much!

7

Niestety nie działa to prawidłowo:

Field parameters.pbix (1.4 MB)

1
11278Ă—725 30.6 KB

1 Like
8

Hi @Harris,
I don’t know the following approach where the hierarchies are generated to match the order of the conditions may work for you.

image
image903Ă—464 13.1 KB 

Share_% = 
VAR CurrentUnits = [AC] 
VAR _ALL_Units_of_Product = CALCULATE(
    [AC],
    ALLSELECTED( Produkty[Nazwa Produktu] )
) 
VAR _all_units_of_cathegory = CALCULATE( 
    [AC], 
    ALLSELECTED( Produkty[Podkategoria] ) ) 

VAR _ALL_Units_of_dzielnica = CALCULATE(
    [AC],
    ALLSELECTED( Dzielnice[Dzielnica Nazwa], Dzielnice[Sortowanie] )
     )

VAR _all_units_region = CALCULATE(
    [AC], 
    ALLSELECTED( Dzielnice[Region] ) ) 

VAR _denominator = SWITCH(
    TRUE(),
    ISINSCOPE( Produkty[Nazwa Produktu] ),
    _ALL_Units_of_Product,
    ISINSCOPE( Produkty[Podkategoria] ),
    _all_units_of_cathegory,
    ISINSCOPE( Dzielnice[Dzielnica Nazwa] ),
    _ALL_Units_of_dzielnica,
    ISINSCOPE( Dzielnice[Region] ),
    _all_units_region,
    _all_units_region
) 
VAR _Result = DIVIDE( CurrentUnits, _denominator, 0 ) 

RETURN
_Result

Regards

Field parameters_JAFP.pbix (1.4 MB)

1 Like