Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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.
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
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.
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?
Try this, but it will give you max only in matrix's total as well.
Measure = MAXX( 'Table', 'Table'[Cost Amount])
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?
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.
Much thanks!
You are welcome.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
100 | |
78 | |
76 | |
52 |
User | Count |
---|---|
144 | |
109 | |
108 | |
88 | |
61 |