1

GOAL: I'm trying to create a table in Power BI with different measure.

Every measure has a different filter, for example:

Realised Savings Azure App Service v2 = 
CALCULATE(SUM('Usage details'[costInBillingCurrency]), FILTER ( 'Usage details', 'Usage details'[meterCategory] = "Azure App Service" && CONTAINSSTRING('Usage details'[meterName.1], "v2") ))

Realised Savings Azure App Service v3 = 
CALCULATE(SUM('Usage details'[costInBillingCurrency]), FILTER ( 'Usage details', 'Usage details'[meterCategory] = "Azure App Service" && CONTAINSSTRING('Usage details'[meterName.1], "v3") ))

Realised Savings DTU = 
CALCULATE(SUM('Usage details'[costInBillingCurrency]), FILTER ( 'Usage details', 'Usage details'[meterCategory] = "SQL Database" && CONTAINSSTRING('Usage details'[meterName.1], "DTU") ))

Realised Savings vCore = 
CALCULATE(SUM('Usage details'[costInBillingCurrency]), FILTER ( 'Usage details', 'Usage details'[meterCategory] = "SQL Database" && 'Usage details'[meterName.1] = "vCore" ) )


Realised Savings VMs = 
CALCULATE ( SUM ( 'Usage details'[costInBillingCurrency] ), FILTER ( 'Usage details', 'Usage details'[meterCategory] = "Virtual Machines"  ) )

PROBLEM: I have checked the totals and they are alright. I'm happy, I reached my goal!

But when I try to put them into a table, horror: they create columns instead of rows:

enter image description here

I don't want to have them like that.

What I want is this:

MeterCategory Total
Realised Savings Azure App Service v2 4147.091
Realised Savings Azure App Service v3 4889.731
Realised Savings DTU 12915.273
Realised Savings vCore 238.528
Realised Savings VMs 5859.370

So how to pivot this table?

I tried the Matrix but it seems like it's not working.

Should I create a table instead of 5 different measures?

Davide Bacci
  • 16,647
  • 3
  • 10
  • 36
Francesco Mantovani
  • 10,216
  • 13
  • 73
  • 113

1 Answers1

2

Use a matrix and switch values to rows.

enter image description here

Davide Bacci
  • 16,647
  • 3
  • 10
  • 36
  • 1
    You saved my day again, David. <3 – Francesco Mantovani Feb 28 '23 at 13:52
  • Strangely both subtotals are enabled but the Raw subtotal is not showing: https://snipboard.io/0QgKir.jpg Do you know why? – Francesco Mantovani Feb 28 '23 at 13:58
  • 1
    You'll need to approach it differently if you want to total all your measures because totals don't work that way in PowerBI. If you want a super simple approach, just add another measure which sums your other 5 measures and call it "Total". – Davide Bacci Feb 28 '23 at 14:01
  • Just curious, how would you approach it differently? Would you create a table? With one DAX per row? – Francesco Mantovani Feb 28 '23 at 14:25
  • I can think of quite a few ways and it all depends on your requirements and how you intend to use the data. You could do this as a calculation group for instance or as a single measure and switch statement or using a disconnected table. It depends what your model looks like and the end requirements so no single answer I'm afraid. – Davide Bacci Feb 28 '23 at 14:28
  • In fact, why don't you make 'Usage details'[meterCategory] a dimension and have just a single measure doing all the work. It will give you totals for each category and also grand totals too. – Davide Bacci Feb 28 '23 at 14:30