DAX FILTER VALUES conflicting with each other.

[Anonymous]

Hello, I am having an issue where one or two filters will work correctly, but then the a third will not.

I am working with Survey data that can be filtered by Gender (Male, Female), Age Brackets (16-25, 26-39, etc.), and States (Australia).

I have been running into an issue when trying to filter the results by these three categories my calculated results vary from the expected.

The code I am using to calculate my results in DAX is below, and the desired code below that.

TR6 Top None = SUM(MAT_Rank_Top_TR6[true_weight])
/
CALCULATE(
    SUMX(MAT_Rank_Top_TR6,MAT_Rank_Top_TR6[true_weight]),
    ALLEXCEPT(MAT_Rank_Top_TR6,MAT_Rank_Top_TR6[Wave])
)

TR6 Top ALL = SUM(MAT_Rank_Top_TR6[true_weight])
/
CALCULATE(
    SUMX(MAT_Rank_Top_TR6,MAT_Rank_Top_TR6[true_weight]),
    ALLEXCEPT(MAT_Rank_Top_TR6,MAT_Rank_Top_TR6[Wave]),
    VALUES(MAT_Rank_Top_TR6[SC1]),VALUES(MAT_Rank_Top_TR6[Age_Brackets]),VALUES(MAT_Rank_Top_TR6[State])
)

However, I find that as I add different conditionals, my results for certain categories vary from what it should be.

I have checked the data in other ways and have confirmed that "TR6 Top None" shows the correct values when no filters are applied. As does "TR6 Top Sex", however for both Age and State there are variations. If I apply either the Male or Female filter, then the data is only correct for "TR6 Top Sex" and incorrect for the rest. 

This issue has shown up several times across my dashboard build, but not for all. 

Here is some Example Data of the last 3 waves. I don't understand why this happens with some of my data and not all. If someone can replicate my issue and solve it, I would appreciate it.

Thanks. 

[Jos_Woolley · Accepted solution]

I would just use something like:

TR6 Top = 
DIVIDE(
    SUM( MAT_Rank_Top_TR6[true_weight] ),
    CALCULATE(
        SUM( MAT_Rank_Top_TR6[true_weight] ),
        ALL( MAT_Rank_Top_TR6[response_label] )
    )
)

Regards

[Jos_Woolley]

Hi,

My first observation would be that it seems a touch unusual that you are using an iterator (SUMX) for your calculations here. But then you haven't explained in layman's terms what it is your measures are calculating, so it's difficult to be sure of how to help you here. Can you clarify what the TR6 Top None and TR6 Top ALL measures are supposed to be calculating? Also, you haven't shared your definitions for the Top Sex, Top Age and Top State measures.

Regards

[Anonymous]

Hi,

Thanks for the reply.

I forgot to mention I am rather new to DAX coding, I have taken over this dashboard from a previous person and I was copying over code from their dashboard to mine, so if they used SUMX somewhere, I used it there too. Are you saying that I do not need to use SUMX in this code? 

To answer your other questions, I want a DAX code that gives me the percentage of respondents that have answered each category across a wave. For example: How many people chose "Price" during Wave 113.

TR6 Top None is my attempt at writing a code that gives me the correct results when none of the filters are applied, however when any of the slicers are selected, the results are not right.

TR6 Top ALL is to show a code that I have used in other parts of my dashboard and the filters are working correctly. For example, I can apply a filter of Females Aged 16-25 from NSW and I can be sure that the data has been filtered correctly.

Top Sex, Age, and State measures are the same as the other two, but with only the corresponding filter as their title. 

VALUES(MAT_Rank_Top_TR6[SC1]),VALUES(MAT_Rank_Top_TR6[Age_Brackets]),VALUES(MAT_Rank_Top_TR6[State]

[Jos_Woolley]

Using the csv data you posted, can you point out a single example for which TR6 Top None gives incorrect results, according to you?

Regards