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...
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!
AllisonKennedy
Community Champion
Community ChampionAnonymous
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])
RETURN
CALCULATE([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-binning
See attached your file with my edits below my signature. Hope it helps!