Forum Discussion

M_SBS_6's avatar
M_SBS_6
Helper V
1 year ago
Solved

Filtered Selection and All selected

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%.

           

 

Need Help
  • bhanu_gautam's avatar
    bhanu_gautam
    1 year ago

    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.

  • Anonymous's avatar
    Anonymous
    1 year ago

    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.

     

2 Replies

  • bhanu_gautam's avatar
    bhanu_gautam
    Super User
    1 year ago

    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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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.