Update a Percent Measure with Multiple Slicer Selections

[mkusf]

Hi there! I have a table where I want to update the values of "Percent Values" Measure at each slicer (a user can select either one, or multiple slicers values). At the moment, I have implemented a measure where Year, Month and the measure gives correct values:

However I am not sure what I can do to have % values update with multiple selection. For example, here the percent values didn't calculate for Race and Year, Month:

I made change to my existing DAX (that gives correct values based on Year, Month) so that it can capture Race as well. But the results are wrong.

Percent Values = 
VAR num =
   CALCULATE( SUM ( question[Count Values] ))

-- this one filters month and year
VAR fmy =
    FILTER (
        -- clear all filter from our table
        ALL ( question ),
        -- and keep only the values matching the
        -- filter context's current month and year
        question[Date].[Month]
            = MAX ( question[Date].[Month] )
            && question[Date].[Year]
                = MAX ( question[Date].[Year] )
            --|| question[Race]
            --    = MAX ( question[Race] )
            --|| question[Gender]
            --    = MAX( question[Gender] ) 
            --|| question[visa_country]
            --    = MAX( question[visa_country] ) 
    )

VAR fr =
    FILTER (
        -- clear all filter from our table
        ALL ( question ),
        -- and keep only the values matching the
        -- filter context's current month and year
         question[Race]
                = MAX ( question[Race] )
    )

VAR denom =
    IF (
        -- checking if Month is in current filter context
        ISINSCOPE ( question[Date].[Month] ), --|| ISINSCOPE ( question[Race] ) || ISINSCOPE ( question[Gender] ) || ISINSCOPE (question[visa_country] ),
           
        -- if yes, use the filter fmy (calculate denom for this quarter
        CALCULATE (
            SUM ( question[Count Values] ),
            ALLSELECTED (),
            fmy
        ),
        --IF (
        --        ISINSCOPE ( question[Race] ),
        --        CALCULATE (
        --            SUM ( question[Count Values] ),
        --            ALLSELECTED (),
        --           fr
        --        ),
            -- else, calculate the denom for all values
            -- this could also be BLANK() or some other calculation
        --    CALCULATE (
        --        SUM ( question[Count Values] ),
        --        ALLSELECTED ()
        --    )
        --)
		CALCULATE (
                SUM ( question[Count Values] ),
                ALLSELECTED ()
            )
    )

RETURN
    DIVIDE (
        num,
        denom
    )

In the above photo, the % values aren't being recalculated. If somebody can provide some hint or direction, i would immensely appreciated it. I did come across several relevant posts, unfortunately I couldn't gather much info from them.

here is the data in .csv format:

Race,Gender,visa_type,Count Values,visa_country,Processing Type,Date
White,Female,C,1,Canada,Custodial,2/14/2014 0:00
Other,Male,M,5,Mexico,Express,1/20/2015 0:00
Hispanic,Male,R,6,Russia,Refugee,2/18/2014 0:00
White,Female,B,4,Brazil,Asylum,3/7/2014 0:00
Hispanic,Male,C,1,Canada,Refugee,4/11/2015 0:00
White,Female,R,7,Russia,Custodial,4/23/2014 0:00
White,Male,M,9,Mexico,Express,4/1/2014 0:00
Hispanic,Male,B,3,Brazil,Refugee,4/13/2014 0:00
White,Female,R,1,Russia,Express,7/31/2014 0:00
White,Male,C,7,Canada,Asylum,9/6/2015 0:00
White,Female,M,2,Mexico,Express,7/22/2014 0:00
Black,Female,B,5,Brazil,Custodial,8/13/2014 0:00
White,Male,R,1,Russia,Asylum,12/9/2014 0:00
White,Female,M,4,Mexico,Asylum,12/6/2014 0:00
Black,Female,B,6,Brazil,Express,12/13/2015 0:00
White,Male,C,7,Canada,Refugee,10/26/2014 0:00
Hispanic,Female,R,5,Brazil,Refugee,9/6/2015 0:00
White,Male,B,9,Mexico,Asylum,9/6/2015 0:00

xlsx file here: https://github.com/TestingGround00/powerbi_question/blob/main/input_data_table.xlsx

[parry2k]

@mkusf this will do it seems like you are overcomplicating it. it is not clear if you are working with one table or you have multiple tables that have relation but still try this, when you post questions like this, do share how you are data model looks like, it is critical to answering any question. Read this post to get your answer quickly.

https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

Sum Value = SUM ( Table[Count Values] ) //assuming count value is a column

% Value = 
DIVIDE ( 
    [Sum Value],
    CALCULATE ( [Sum Value], ALLSELECTED ( ) )
)

✨ Follow us on LinkedIn

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[mkusf]

Hi @parry2k ! Thank you for helping me out. I edited my question with a data set. I did not change any of the data types. I took default types from the Power BI. The issue that I have is, in visulization all the markers for a month and year need to add to 1.0 (or 100) with and without slicer (without slicers is by Month & Year).

I just wish Power Bi had a % options for Line graphs 😞 Please let me know if I can explain things any better. I do have some language barrier. Although what you gave me does add to 100 in whole subset, it really needs to add in for each Month, Year.

[AlexisOlson · Accepted solution]

I think you can add back in the year-month filter context like this:

% Value =
DIVIDE (
    [Sum Value],
    CALCULATE (
        [Sum Value],
        ALLSELECTED (),
        VALUES ( dimDate[Year] ),
        VALUES ( dimDate[Month] )
    )
)

[mkusf]

Oh wow. Such an elegant solution. I lost some scalp in the process. May I please know how can I get better at writing DAX? I am pretty brand new and am overwhelemed.

Thanks a ton for your help (thank you @parry2k too)!

[AlexisOlson]

I learned from years of answering questions like yours and always trying to find a more elegant solution than the first one that I came up with. If it seems like there should be a better way, usually there is.

[mkusf]

I definitely need to be regular here. At the moment I just look for how to do that, how to do this....but I feel like I am everywhere. The basic course I took wasn't that great. Appreciate your help again Alexis! Enjoy the holidays.