sum of unique values with two filters

[danielcontente]

Hi, 

I have a table as below, and my goal is to have a Matrix table thats shows the cost of a "Campaign breakdown 2" per year.

The problem is that i have multiple values for each Campaign Breakdown2.

Would somebody be able to help with it?

Let mw know if you need the CSV.

Thank you

[v-janeyg-msft]

Hi, @danielcontente 

You can create a single summraize table then use it to show the result in matrix visual.

 Like this:

Table = SUMMARIZE(Table1,[Campaign Breakdown 2],[Campaign],"amount",MAX(Table1[Amount]))

Did I answer your question ? Please mark my reply as solution. Thank you very much.
If not, please feel free to ask me.

Best Regards,

Community Support Team _ Janey

[sm_talha]

You can Group By in the power query based on "Campaign Breakdown 2" and "Campaign Created Year" with Operation = Max to achieve your result. (I would suggest you to do this with a duplicate of your table because the main table could be used for other metric in its original form)

This will give you a table like this: 

Now you can use columns from this grouped table to achieve your desired result. 

[danielcontente]

Hi @sm_talha thanks for helping.

However, by cretaing another table with a group by, i will not have these metrics on my fact table, which will stop me of building a unified visual.

Do you  have another alternative?

[sm_talha]

Try this, but it will give you max only in matrix's total as well. 

Measure = MAXX( 'Table', 'Table'[Cost Amount])

[danielcontente]

Yes.

I have done it using the MAX formula, but the total for the year is also the max.

Is there anyway i can obtain the same result but with the total in the Matrix?

[Ashish_Mathur · Accepted solution]

Hi,

To your matrix visual, drag Campaign Breakdown 2 and Campaign Year.  Write these measures:

Cost = max(data[cost amount])

Total cost = SUMX(values(Data[Campaign Breakdown 2]),[Cost])

Drag the second measure to the visual.

If this does not help, then share the link from where i can download your PBI file.

[danielcontente]

Much thanks!

[Ashish_Mathur]

You are welcome.