PowerBI/DAX - summing up group membership

Question

I'm very new to powerBI and DAX. I have a challenge which is probably quite simple but I cannot figure it out.

To simplify, my data is basically a table of doctors, dates and points. Each doctor has a distinct and fixed region:

Doctor  Region  Points  Date
John Doe    A   3   2020-05-10
John Doe    A   2   2020-05-10
John Doe    A   4   2020-05-11
Mary Doe    A   1   2020-05-10
Mary Doe    A   3   2020-05-11
Jane Smith  B   1   2020-05-10
Jane Smith  B   1   2020-05-10
...etc...

It is simple for me to display this data as a line chart with date on the x-axis (using a date lookup table), and sum of points on the y-axis, where the doctor can be selected individually in an associated slicer.

Here is the challenge. I need to be able to chart both the sum for doctor and the sum of all points in the selected doctor's region (i.e. two lines on the chart, plotted by date) just by selecting the doctor on a single slicer.

I have experimented with creating a new column

report_points_all = CALCULATE( SUM(MyTable[points]), ALL(MyTable) )

But this is giving me nonsense data with enormous sums. I need to filter it down by the region associated with the selected doctor but don't have enough DAX skill to figure it out. Can anyone help point me in the right direction? Thanks in advance

Giovanni Luisotto Giovanni Luisotto · Accepted Answer · 2020-05-19T09:10:45

Your current formula is not that far from working

report_points_all = CALCULATE( SUM(MyTable[points]), ALL(MyTable) )

But ALL(table) removes the filters on the whole table (doctors, date, region, etc), that's why you are probably getting one huge constant value.

What you want to do is removing the filter only on the the Doctor column

report_points_all = CALCULATE( SUM(MyTable[points]), ALL('MyTable'[Doctor]) )

PowerBI/DAX - summing up group membership

1 Answers