How to summarize measures in a matrix

patrabas — Mon, 06 Jun 2022 14:41:21 GMT

Hello,

I'm new to PowerBi, here is the pbix I'm using test.pbix (I cannot find the attach file button in this post ☹️).

I'm aiming to create this table using PowerBi (as you can see it's summarized):

My database looks like this:

Given two input dates: month1 (calendar_init) and month2 (calendar_end), I need to do the following calculations. I have split my problem in 3 parts:

BASELINE --> Get the row where type = "baseline" and month = month1

FULL PERIOD COST --> Add a new row whose values are all 0 except type="Full Period Cost (existing VMs)" and whose period cost is (yearlyCost/365)*30-periodCost where ow is BASELINE previously calculated

REST OF ROWS --> Select (yearlyCost/365)*30 where month >= month1 and month <= month2 and type <> "baseline"

I create a new table called Movements which is the union of the 3 parts described above as it follows:

But since I'm using measures I cannot summarize them as the reference to the measures get lost...

How can I group them so I don't have multiple rows with the same type?
How can I totalize each column?

Thanks in advance

Re: How to summarize measures in a matrix

Anonymous — Thu, 09 Jun 2022 09:19:52 GMT

Hi patrabas

You can through SUMX() to summarize measures . You can refer to the following 2 cases .

https://community.powerbi.com/t5/DAX-Commands-and-Tips/sum-of-a-calculated-measure/m-p/948738

https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/m-p/63376#U63376

Best Regards,
Community Support Team _ Ailsa Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

topic Re: How to summarize measures in a matrix in DAX Commands and Tips

How to summarize measures in a matrix

Re: How to summarize measures in a matrix