Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Update a Percent Measure with Multiple Slicer Selections

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

  • 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] )
        )
    )

     

6 Replies

  • Anonymous 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.

    • Anonymous's avatar
      Anonymous
      Not applicable

      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's avatar
        AlexisOlson
        Icon for Super User rankSuper User

        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] )
            )
        )