Issues with implementing grouped percentage with ALLEXCEPT

[Anonymous]

Hello! I wanted to get a grouped Percentage as a New Column or New Measure (which is recommeneded from what I have read in the forum). I have a data where user would use Slicers to get various Percentages. However, at the moment my query is keep failing. I have visited the forum and I can't seem to figure out how to Group By and get the percentage of the group. Furthermore, I can't use the Group By tool in the Query Editor because my data is quite large. So, I have to rely on DAX which I don't have lot of knowlede about.

Here is the measure I created which doesn't work:

Percent Values = CALCULATE (
    SUM (question[Count Values] ),
    ALLEXCEPT ( question, question[Date].[Quarter], question[Date].[Year],question[Processing Type],question[Gender], question[Race] )
)
    / CALCULATE (
        SUM ( question[Count Values] ),
        ALLEXCEPT ( question, question[Date].[Quarter], question[Date].[Year],question[Processing Type],question[Gender], question[Race] )
    )

Here you can see, the Percent Values are all 1:

Please find the exccel data file here:

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

I was expecting to get a result like this:

Any help is immensly appreacited. Thank you.

[amitchandak]

@Anonymous , Both can not be same , either above one will not have allexcept

Percent Values = CALCULATE (
    SUM (question[Count Values] )
)
    / CALCULATE (
        SUM ( question[Count Values] ),
        ALLEXCEPT ( question, question[Date].[Quarter], question[Date].[Year],question[Processing Type],question[Gender], question[Race] )
    )

or

example Replace allexpect with allselected

Percent Values = CALCULATE (
    SUM (question[Count Values] )
)
    / CALCULATE (
        SUM ( question[Count Values] ),
        filter( allselected( question), question[Date].[Quarter] = Quarter(max([Date])) &&  question[Date].[Year] = year(max(question[Date]) ) && question[Processing Type] = max(question[Processing Type]) && question[Gender] = max(question[Gender]) &&  question[Race] = max(question[Race] )  )
    )

Or below will have allselecetd

Percent Values = CALCULATE (
    SUM (question[Count Values] ),
    ALLEXCEPT ( question, question[Date].[Quarter], question[Date].[Year],question[Processing Type],question[Gender], question[Race] )
)
    / CALCULATE (
        SUM ( question[Count Values] ),
        allselected()   )

prefer to use divide function

[Anonymous]

Hi @amitchandak ! Thanks a lot for helping me out with my question. I implemented things according to your suggestion, however I am not sure why the Total percentage row indicates 1.0 eventhough they clearly don't add up to 100% For instance take a look at this example:

For Percentage calculation I used #3 from your suggestion:

Percent Values = CALCULATE (
    SUM (question[Count Values] ),
    ALLEXCEPT ( question, question[Date].[Quarter], question[Date].[Year],question[Processing Type],question[Gender], question[Race] )
)
    / CALCULATE (
        SUM ( question[Count Values] ),
        allselected()   )

I thought measure would calculate based on filters/slicers?!