Calculate Average Metric month over month

[Anonymous]

Hello, 

I'm trying to create a visual to show the average month over month for each priority. 

If you look at this photo below this shows the data I'm working with: 

For each month within the year I need to show the total for each priority and the percentage of met. Feb for example would show: 27 for the total and 81% for the met. I believe this could be done by creating new columns within the dataset and using IF statements to make them a 1 or 0 after which performing an add and divide. However, I believe there is an easier way. 

Thank you for your help! 

[az38 · Accepted solution]

Hi @Anonymous 

try measures

Total for priority = CALCULATE(SUM(Table[Count of Incident Number]), ALLEXCEPT(Table, Table[Year], Table[Month], Table[Priority]) )

and

%Met = 
DIVIDE(
CALCULATE(SUM(Table[Count of Incident Number]), ALLEXCEPT(Table, Table[Year], Table[Month], Table[Priority]), Table[Met MTTR] = TRUE() ),
[Total for priority]
)

[Anonymous]

Hi @az38, 

This is very helpful! I did hit a little bit of a snag with this. It appears this requires a measure within a measure for me to get the count of incident number. I've created a measure to perform that; however, I'm getting the below error: 

Total for priority = CALCULATE(SUM([Count of Incident Number]), ALLEXCEPT(Sheet1, Sheet1[Year], Sheet1[Month], Sheet1[Priority]) )

The SUM function only accepts a column reference as the argument number 1. / Parameter is not the correct type

Have you experienced this before? I also tried to create only 1 line, but still have the same error of incorrect parameter: 

Total for priority = CALCULATE(SUM(COUNT(Sheet1[Incident Number]), ALLEXCEPT(Sheet1, Sheet1[Year], Sheet1[Month], Sheet1[Priority]) ))

Thank you!!

[az38]

Hi @Anonymous 

for using measure inside SUM() try SUMX() function like

Total for priority = CALCULATE(SUMX(Sheet1, [Count of Incident Number]), ALLEXCEPT(Sheet1, Sheet1[Year], Sheet1[Month], Sheet1[Priority]) )

[Anonymous]

@az38, 

Thank you, that worked brilliantly! I was missing the sheet1 at the beginnning. 

[az38]

@Anonymous 

You tried to use SUM that does not need a table but can work only with Columns

SUMX(), COUNTX, AVERAGEX() are working with expressions. you can read here https://docs.microsoft.com/en-us/dax/sumx-function-dax