Filtered Selection and All selected

[M_SBS_6]

Hi, 

I have the following logic which works at an individual FY filter but when all FY are selected, it takes the value of the MAX financial year (23/24) instead of all.

I want my logic to return the % value based on the individual filter selection but when all FY being selected, I want the total but not sure what I need to do to amend my logic. I hope that makes sense.

New_apps =

If( 

MAX( test[dateFY] = "21/22", 

DIVIDE(

test[new_apps 21/22], 

test_one[all 21/22]

),

If( 

MAX( test[dateFY] = "22/23", 

DIVIDE(

test[new_apps 22/23], 

test_one[all 22/23]

),

If( 

MAX( test[dateFY] = "23/24", 

DIVIDE(

test[new_apps 23/24], 

test_one[all 23/24]

),

Test[new_apps_all] / test_one[all]

)))

21/22 = 70.51%

22/23 = 59.30%

23/24 = 47.06%

All FY selected = 47.06% but based on the numbers I have, it should be 90.23%.

[bhanu_gautam · Accepted solution]

@M_SBS_6 ,

To achieve the desired behavior where the logic returns the percentage value based on the individual filter selection and the total when all FYs are selected, you can modify your DAX formula to check if all FYs are selected and then calculate the total accordingly. Here is an updated version of your DAX formula:

DAX
New_apps =
IF(
ISFILTERED(test[dateFY]),
SWITCH(
TRUE(),
MAX(test[dateFY]) = "21/22", DIVIDE(test[new_apps 21/22], test_one[all 21/22]),
MAX(test[dateFY]) = "22/23", DIVIDE(test[new_apps 22/23], test_one[all 22/23]),

In this formula:

ISFILTERED(test[dateFY]) checks if the dateFY column is filtered.

[v-xinruzhu-msft · Accepted solution]

Hi,

Thanks for the solution bhanu_gautam  offered, and i want to offer some more information for user to refer to.

hello @M_SBS_6 , you can try the following measure.

New_apps =
IF (
    ISFILTERED ( test[dateFY] ),
    IF (
        SELECTEDVALUE ( test[dateFY] ) = "21/22",
        DIVIDE ( test[new_apps 21/22], test_one[all 21/22] ),
        IF (
            SELECTEDVALUE ( test[dateFY] ) = "22/23",
            DIVIDE ( test[new_apps 22/23], test_one[all 22/23] ),
            IF (
                SELECTEDVALUE ( test[dateFY] ) = "23/24",
                DIVIDE ( test[new_apps 23/24], test_one[all 23/24] )
            )
        )
    ),
    Test[new_apps_all] / test_one[all]
)

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[bhanu_gautam · Accepted solution]

@M_SBS_6 ,

To achieve the desired behavior where the logic returns the percentage value based on the individual filter selection and the total when all FYs are selected, you can modify your DAX formula to check if all FYs are selected and then calculate the total accordingly. Here is an updated version of your DAX formula:

DAX
New_apps =
IF(
ISFILTERED(test[dateFY]),
SWITCH(
TRUE(),
MAX(test[dateFY]) = "21/22", DIVIDE(test[new_apps 21/22], test_one[all 21/22]),
MAX(test[dateFY]) = "22/23", DIVIDE(test[new_apps 22/23], test_one[all 22/23]),

In this formula:

ISFILTERED(test[dateFY]) checks if the dateFY column is filtered.