Calculated Measure showing on all rows of table in report

[siemdevlieger]

Hi all,

I have not so much an issue, but something I am trying to understand, as it might have an impact on areas that I am not aware off.

I have one table with sales by scenario (actual, budget, projection) which is joined to a dimension table containing all possible scenario’s (many to one relationship). Below is just dummy data, but the same is happening for my full dataset.

Transaction table:

Year	Scenario	Value
2019	Actual	1000
2019	Budget	900
2019	Projection	975

DIM_SCENARIO table:

Scenario	Scenario_description
Actual	Actual
Budget	Target
Projection	Forecast

Calculated Measure:

Sales = calculate(sum(Transaction table'[Value]))

Sales Actual = calculate(sum(Transaction table'[Value]),DIM_SCENARIO[Scenario] = "Actual")

When I now pull these measures in a table together with the ‘Scenario’ from the DIM_SCENARIO table it looks like the below picture. It shows all scenarios, but also shows ‘Sales Actual’ for each scenario. Why does it show the value for all scenario’s and not only for the ‘Actual’ scenario? I would expect it to show zero or null for the other scenario’s as that is what the measure is filtered on. The total of the table is however showing the correct value, but it looks confusing.

Can anyone explain in easy language why it’s showing this way? Should I use a different formula for my measure?

I could not find something online describing exactly the same as this.

Thanks in advance!

[v-qiuyu-msft · Accepted solution]

Hi @siemdevlieger, 

As you set the filter condition DIM_SCENARIO[Scenario] = "Actual" in the measure Sales Actual, the measure will calculate rows which related Scenario is Actual. To achieve your requirement, you can modify the measure below: 

Sales Actual = IF(MAX('DIM_SCENARIO'[Scenario])="Actual",calculate(sum('Transaction table'[Value]),DIM_SCENARIO[Scenario] = "Actual"),BLANK())

Best Regards,
Qiuyun Yu 

[siemdevlieger]

Thanks a lot Qiuyun!

This is indeed working and giving me the outcome that I was expecting.

Understand what you are saying and will follow your advice for setting up these measures!

Best regards,

Siem