DAX | Calculated column | Filters

[BramVDM]

Hi all,

I have been struggling with the following for a while now, and would appreciate your help. I will try to explain myself as clearly as possible, but should anything need further explanation, please do let me know. Also, I added an extract of what I currently have created for you to have a better idea; I am - unfortunately - unable to provide the data for confidentiality reasons.

OUTPUT: What I am trying to achieve is a table or matrix, where the first column lists all the indicators that we work with. The second column gives a brief description of the indicator. The third column will eventually be hidden, but I added it here for the sake of being complete. Every indicator measures in a certain way, e.g. it belongs to AnswerCategory "Top45" if the indicator measures the percentage of people who picked either answer option 4 or answer option 5 in a certain survey. Then, the column of interest, is Moyenne PZ. I would like it, for each indicator (hence, for each row), to give the percentage of people (within a certain PZ (priority zone), and only for poll no. 13) that belongs to that answer category.

The calculated column is defined as follows:

Moyenne PZ =
IF('Connector - Data Sources'[AnswerCategory] = "Top1";
    (CALCULATE([Percent Top1]; FILTER('Data - HHI-P07-P13'; 'Data - HHI-P07-P13'[Poll #] = "13"); FILTER('Connector - Sub-PZs'; 'Connector - Sub-PZs'[PZ] = "1")));
    IF('Connector - Data Sources'[AnswerCategory] = "Top12";
        (CALCULATE([Percent Top1&2]; FILTER('Data - HHI-P07-P13'; 'Data - HHI-P07-P13'[Poll #] = "13"); FILTER('Connector - Sub-PZs'; 'Connector - Sub-PZs'[PZ] = "1")));
        IF('Connector - Data Sources'[AnswerCategory] = "Top23";
            (CALCULATE([Percent Top2&3]; FILTER('Data - HHI-P07-P13'; 'Data - HHI-P07-P13'[Poll #] = "13"); FILTER('Connector - Sub-PZs'; 'Connector - Sub-PZs'[PZ] = "1")));
            IF('Connector - Data Sources'[AnswerCategory] = "Top45";
                (CALCULATE([Percent Top4&5]; FILTER('Data - HHI-P07-P13'; 'Data - HHI-P07-P13'[Poll #] = "13"); FILTER('Connector - Sub-PZs'; 'Connector - Sub-PZs'[PZ] = "1")))

Furtermore, the calculation for e.g. Top4&5, is as follows, just a percentage, but - once again - added it to be complete.

Percent Top4&5 = Countrows(Filter('Data - HHI-P07-P13';'Data - HHI-P07-P13'[Answer-Codes - HHI.Answer-Code]="4"||'Data - HHI-P07-P13'[Answer-Codes - HHI.Answer-Code]="5"))/Countrows('Data - HHI-P07-P13')
     ))))

As you can see from the extract below, however, the third column is not doing quite what I would want it to do... The percentages are the same (or multiples of the same) value depending on the AnswerCategory, so I fear that it completely disregards the Indicator ID (int)? What I would like it to do, e.g. for the first indicator, it performs the calculation of type Top45, but only for those values that correspond to that same indicator...

Many thanks in advance,

Bram

[v-yuta-msft]

Hi BramVDM,

 It seems like Data -HHI-P07-P13 is another table in your datasource and you have build relationship between the two tables, right?

Please add a filter condition to your DAX like this pattern.

ALLEXCEPT (
        'Connector - Data Sources',
        'Connector - Data Sources'[Indicator ID (int)]
    )

Regards,

Jimmy Tao

[Greg_Deckler]

Can you post example/sample data that can be copied and pasted. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490