Average with DAX

[petrutjim]

Hi,

I have this data set:

I want to create a bar chart, with Phase as category, like this:

I need to have Role and Date filters on the dashboard.

What I want to achieve is: when All is selected on Role filter, I need to count John only once (using the sum of Flag column), even dough he is present twice in the same Phase (e.g. he is in CS phase on the Dev and on the SR roles). So the chart will have sum of Flag "2" on each bar.

If I select each particular Role from the filter, John should be summed up only once in each of the Role. So lets say I select each role and summarize the numbers on the bars for each role, by hand, I will end up with the numbers in the chart above (3 for CS, 3 for HI and 2 for TI).

My idea is to create a measure that does an Average by Role of the Flag column, and this will enable the scenario I want, but the only problem is I don't manage to put the formula in place.

Please keep in mind that the Phase column is actually in another dimension table, and in the fact table I have foreign keys to it.

Also I must use only the Flag column in order to do the SUM, not any other column. (because this problem could be solved using CountDistinct of the Name, but I don't want this).

Thank you so much for reading this!

[petrutjim]

I have found the solution for my problem.

Sum of Flag = CALCULATE

           (SUMX

                  (SUMMARIZE(Table, Table[Name], "Average", AVERAGE(Table[Flag])),[Average])

           ,Table[Flag] =1 )

[amitchandak]

@petrutjim , not very clear

seems like

countrows(summarize(Table, [Name], [role],[phase]))

[petrutjim]

this won't work because I need to user the Flag column in order to calculate the sum.

Let me rephrase a bit.

If you count distinct on Name, you will get 2 for CS, 2 for HI and 2 for TI, with All filter on Role. Right?

I want to achieve the same, but using Sum of Flag column.