How to make a SUM of a conditional Metric

[josesonnen]

Hi everyone, I'm kind of stuck and I need your help. I'm working on a report were I have the following data. 

The Alert 1 is a new meausure created by me. What I need now is to add up all the daily alerts to show the total for each name, but I don't know how to do it. I have a separate table for calendar date and I can't create new tables o columns because I'm working with a live connection to sql. 

Any ideas??

Thank you! 

Name	Date	Metric 1	Avg last 7 days	Alert 1
AAAABBBB	15/07/2020 00:00	97	88	1
AAAABBBB	16/07/2020 00:00	56	84	1
AAAABBBB	17/07/2020 00:00	65	79
AAAABBBB	18/07/2020 00:00	77	80
AAAABBBB	19/07/2020 00:00	78	76
AAAABBBB	20/07/2020 00:00	62	73
AAAACCCC	16/07/2020 00:00	1	2	1
AAAACCCC	17/07/2020 00:00	4	3	1
AAAACCCC	18/07/2020 00:00	1	2	1
AAAACCCC	20/07/2020 00:00	2	2

[v-lili6-msft · Accepted solution]

hi  @josesonnen 

This looks like a measure totals problem. Very common. See this post about it
https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907

For your case, just create another measure as below:

Total = 
VAR _table = SUMMARIZE('Table',[Name],[Date],"_value",[Alert 1])
RETURN
SUMX(_table,[_value])

Regards,

Lin

[Sansudhi8]

Hi,

I think simple Metrix table will sort out this. 

PBI. 

[josesonnen]

I tried but it's not workink. 

The alert 1 is not a column, is a conditional measure that I created. 

The only data that I have on the database is Name and Metric 1. Avg Last 7 days and Alert 1 are measures, so what I need is to add all the alerts per day to get the total per week for each name

[amitchandak]

@josesonnen , what is expected output , it is not clear.

[josesonnen]

I want to get a table with every name a and the sum of the alerts for the last week. 

Name	Sum of Alerts 1
AAAABBBB	2
AAAACCCC	3

If I use the conditional measure that I created it doesn't sum up, it only shows me average.