% incorrectly updating when slicing - ALLEXCEPT DAX

[Anonymous]

Hi,

I've created a measure to show me the percentage of responses to a particular question on a survey, over a number of years...

Measure = SUM('FYE Data (2)'[Response]) / CALCULATE(sum('FYE Data (2)'[Response]), ALLEXCEPT('FYE Data (2)', 'FYE Data (2)'[Year]))

Example of the table below...

When I drop the measure into s stacked bar chart and update to the corresponding year eg. 2020, the measure displays the correct percentages. However when I slice on additional years, eg. 2019 and 2020, the % displayed alters slightly, albeit by a few %. Any ideas what I'm doing incorrectly with the measure?

[AntrikshSharma]

@Anonymous  Are you slicing the data by the Dates table? if yes, try something like this:

=
DIVIDE (
    SUM ( 'FYE Data (2)'[Response] ),
    CALCULATE (
        SUM ( 'FYE Data (2)'[Response] ),
        ALL ( 'FYE Data (2)' ),
        VALUES ( Dates[Calender Year Number] )
    )
)

[Anonymous]

@AntrikshSharma 

Thanks for your suggestion. I wasn't slicing by the calendar table but tried with your suggestion and the same behaviour as described above happened.

I'm not entirely why this would not be working as it seems a relatively straightforward measure/calculation

[Anonymous]

Many a time problems with DAX stem from the fact that the model is incorrect. Please shape your model according to Best Practices and everything, I mean EVERYTHING, will not only be easier. It'll be much faster and easy to understand. What you want to do is to turn your model into a star schema.

[Anonymous]

Please read this

https://docs.microsoft.com/en-us/power-bi/guidance/star-schema#star-schema-overview

to know how to implement good models.

[amitchandak]

@Anonymous , allexcept work like % of subtotal . means you will get % within year

Measure = divide(SUM('FYE Data (2)'[Response]) , CALCULATE(sum('FYE Data (2)'[Response]), ALLEXCEPT('FYE Data (2)', 'FYE Data (2)'[Year])))

All will work for % of total

Measure = divide(SUM('FYE Data (2)'[Response]) , CALCULATE(sum('FYE Data (2)'[Response]), ALL('FYE Data (2)')))

refer:https://www.sqlbi.com/articles/using-allexcept-versus-all-and-values/

https://www.sqlbi.com/articles/managing-all-functions-in-dax-all-allselected-allnoblankrow-allexcept/

https://community.powerbi.com/t5/Desktop/Percentage-of-subtotal/td-p/95390

[Anonymous]

Hi @amitchandak 

Thank you for your suggestion too. I tried this also but it came back with the same result eg. the % displayed correctly when slicing on one year...

...but slicing on on an additional year, then it updates the 2020 values to.

The measure was formatted to %, and then set to show value as % of GT

[harshnathani]

Hi @Anonymous ,

You can just create a measure

Measure =

var a = CALCULATE(SUM('FYE Data (2)'[Response]))

var b = CALCULATE(SUM('FYE Data (2)'[Response]), ALL('FYE Data (2)')

RETURN

DIVIDE (a,b)

Regards,
Harsh Nathani
Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

[Anonymous]

Hi @harshnathani 

Thank you for replying. I tried your suggested measure but it came back as syntax incorrect. Am I inputting it incorrectly?

Thanks

Pete

[Anonymous]

You get a syntax error because the numbers of "(" and ")" in your formula are different. A theorem says that a necessary, albeit not sufficient, condition for a formula to be correct is the same number of the open and close parentheses.

[Anonymous]

@AnonymousThank you - looks like there was a missing ) highlighted in red. Unfortunately that has the same impact as the other suggested solution.

Measure 4 =

var a = CALCULATE(SUM('FYE Data (2)'[Response]))

var b = CALCULATE(SUM('FYE Data (2)'[Response]), ALL('FYE Data (2)'))

RETURN

DIVIDE (a,b)