Forum Discussion
Latest week values for grouped columns
I have created a column to group counts of vulnerable populations in the most current week however my total is not adding up to the values that I get (e.g. below the [outbreak identifier count latest week] = 10 in Table A & 8 in Table B, but both keep calculating as 5)
Table A
Table B
Groupings:
Vulnerable populations Grouping = SWITCH(
TRUE(),
SEARCH("Asylum seekers / refugees",'Outbreaks and Events'[Vulnerable populations],,0)=1,"Direct provision centres",
SEARCH("Migrants (including undocumented)",'Outbreaks and Events'[Vulnerable populations],,0)=1,"Direct provision centres",
SEARCH("People with addictions",'Outbreaks and Events'[Vulnerable populations],,0)=1,"Homeless / those with addiction issues",
SEARCH("Homeless",'Outbreaks and Events'[Vulnerable populations],,0)=1,"Homeless / those with addiction issues",'Outbreaks and Events'[Vulnerable populations])
Count:
Outbreak Identifier Count Latest Week = IF(
CALCULATE(
COUNT('Outbreaks and Events'[Outbreak Week]),FILTER(ALL('Outbreaks and Events'[Outbreak Week]),'Outbreaks and Events'[Outbreak Week] = MAX('Outbreaks and Events'[Outbreak Week])))=BLANK(),0,
CALCULATE(count('Outbreaks and Events'[Outbreak Week]),FILTER(ALL('Outbreaks and Events'[Outbreak Week]),'Outbreaks and Events'[Outbreak Week] = MAX('Outbreaks and Events'[Outbreak Week]))))
My ideal scenario would be to have grouped values on Table B but the [outbreak identifier count latest week] adding up to the correct count.
Can anyone help?
File attached
https://www.dropbox.com/s/187y3t2fu3jlaqi/SAMPLE%20Outbreak.pbix?dl=0
Anonymous
Because all your data is in one table, this is not calculating properly. The actual correct answer is 5 from what I can tell. Try using this Measure to see that result:
Outbreak Identifier Count Latest Week =VAR _MaxWeek = MAXX(ALL('Outbreaks and Events'),'Outbreaks and Events'[Outbreak Week])RETURNCALCULATE([Outbreak Count], 'Outbreaks and Events'[Outbreak Week]=_MaxWeek)The reason you get different answers at the individual grouping level is because they had no new cases this week, so it's just calculating their cases for the last week they had cases. I don't think that's the number you want? If it is, you'll need to do something with a SUMX.Also, you can right click on the Vulnerable Populations column and create a New Group, which you may find easier than a DAX SWITCH statement? https://docs.microsoft.com/en-us/power-bi/create-reports/desktop-grouping-and-binningSee attached your file with my edits below my signature. Hope it helps!
1 Reply
Anonymous
Because all your data is in one table, this is not calculating properly. The actual correct answer is 5 from what I can tell. Try using this Measure to see that result:
Outbreak Identifier Count Latest Week =VAR _MaxWeek = MAXX(ALL('Outbreaks and Events'),'Outbreaks and Events'[Outbreak Week])RETURNCALCULATE([Outbreak Count], 'Outbreaks and Events'[Outbreak Week]=_MaxWeek)The reason you get different answers at the individual grouping level is because they had no new cases this week, so it's just calculating their cases for the last week they had cases. I don't think that's the number you want? If it is, you'll need to do something with a SUMX.Also, you can right click on the Vulnerable Populations column and create a New Group, which you may find easier than a DAX SWITCH statement? https://docs.microsoft.com/en-us/power-bi/create-reports/desktop-grouping-and-binningSee attached your file with my edits below my signature. Hope it helps!