Total (Sum) Max Values per Country Filtered by location and date slicers

[mike-frieling]

Hi All,

I need help with this issue that has been bothering me for a few days now. I am currently working on a personal project and have ran into an issue with trying to Total (sum) the max values for selected countries and dates using slicers on my visuals. Here is what I did with the both measures in order to get the max values for countries based on countries selected and date selected via the two slicers on my visual:

--First Measure--

Max Fully Vaccinated =

CALCULATE(

    MAX(CovidVaccinations[people_fully_vaccinated]),

    USERELATIONSHIP(CovidVaccinations[location],CovidDeaths[location]),

    USERELATIONSHIP(CovidVaccinations[date],'Date Table'[Date]),

    ALLSELECTED(CovidVaccinations[location])

)

--Second Measure--

Max People Vaccinated =

CALCULATE(

    MAX(CovidVaccinations[people_vaccinated]),

    USERELATIONSHIP(CovidVaccinations[location],CovidDeaths[location]),

    USERELATIONSHIP(CovidVaccinations[date],'Date Table'[Date]),

    ALLSELECTED(CovidVaccinations[location])

)

Here are my table relationships so you can see why I wrote the above measures the way I did:

Here is a view of the visual currently:

As you can see in the matrix all the columns total (sum) properly except people vaccinated and max fully vaccinated as they are based on the measures that I did above. The reason I had to do measures was due to the way the data is in the tables. For each country and each day it does a running total for the people_vaccinated and fully_vaccinated columns. 

Basically everything works fine when using country and date slicers except for the Total row at bottom for People Vaccinated and Max Fully Vaccinated. HELP!

Thanks 

Mike

[mike-frieling]

The issue isnt having the ability to use country or date selection properly. That all works just fine. The issue I have is I need to Total (Sum) the Max values for all countries whenever they are selected but that doesnt work the current way I have it written. I need to add sumx or summarize however I havent gotten it to work properly and was hoping someone could point me in the right direction. 

Here is an example of the people_vaccinated column:

As you can see here that instead of them entering in the amount of people per day that were vaccinated they did a running total per day per country. This is why I had to create the max measures however when you select multiple countries or all countries it will only show the MAX people_vaccinated for the highest country, in the above case for People Vaccinated the total in the matrix is showing the 1.2b for China and not all the countries selected Total. 

I just need to figure out what to add to the measure to Total (Sum) the max for selected countries. Hopefully this makes sense. 

[johnt75 · Accepted solution]

Try the below. The same principle should work for the Covid Deaths table too

Total vaccinated =
VAR SummaryTable =
    ADDCOLUMNS (
        VALUES ( 'Covid vaccinations'[location] ),
        "@value", CALCULATE ( MAX ( 'Covid vaccinations'[people_vaccinated] ) )
    )
RETURN
    SUMX ( SummaryTable, [@value] )

[mike-frieling]

As you can see below this did not work. The country demension table I have added to clean up having to use Userelationship in the measures however the Total Vaccinations measure doesnt work and still gives me a very large MAX number that has been SUMd it looks like based on entire data for all countries:

I need the measure to only sum the max for that column based on the countries and date timeframes selected... example - for the above matrix chart if all countries are selected for that timeframe then I would need to have the Total Vaccinated column to show the different amounts for each country for the timeframe selected. HELP!

(PS thanks for the country demension table so I could clean up the code on the other measures I am using)
Mike

[johnt75]

You mentioned having to use USERELATIONSHIP in your measures, you need to add that into the CALCULATE in the Total vaccinations measure as well. 

[mike-frieling]

I have removed the need for userelationship in my measures after cleaning up and using the date and country dimension tables. I have added the userelationship to the total vaccinated measure and still doesnt fix the issue with it pulling the max for the highest country during the timeframe and putting that number down for ALL as you can see here:

The column all the way to the right is the Total Vaccinated. Again, I just need a measure that will show me the max for the countries selected and the date timeframe selected and sum (total it). Here is what the relationships look like now:

No more many-to-many relationships. Any other ideas???

[mike-frieling]

Since this is a personal project I can send you the power bi file if you like to really mess with it yourself to try and find solution? Let me know 

[mike-frieling]

I believe I got your Total Vaccinated measure to work! I had to fix relationship from world population table to Country Table and then use Country Table location in the measure. Seems to be working properly now! Thank you!

[johnt75]

The problem is likely caused by the many-to-many bidirectional relationship. Try creating a country dimension table which you can then link to both vaccinations and deaths, and use the columns from that table in all your visuals.

You could create a dimension table with something like

Country dimension =
DISTINCT (
    UNION (
        ALLNOBLANKROW ( CovidDeaths[country], CovidDeaths[continent] ),
        ALLNOBLANKROW ( CovidVaccinations[country], CovidVaccinations[continent] )
    )
)