Two calculated measures not working as intended

[jake88]

I have two calculated measures which aren't quite working as expected:

The measures are below: M3Q is the latest forecast round and Key Comparator is the field that we are comparing the M3Q to.

M3Q = calculate(sum('PBI DATA'[Value]),'PBI DATA'[Event]="M3Q")/1000000

Key Comparator = CALCULATE(sum('PBI DATA'[Value])/1000000)

The issue I'm having is that when I select an event from my slicer, the Key Comparator changes as expected however the M3Q also changes. I think the reason behind this is that some of the key comparator events have less data than the M3Q.

For Example:

M3Q and M2Q has data for all fields in the Type column (Opex, Capex, and Revex) but M1Q only has data for Opex. When I select M2Q as the key comparator the M3Q number is correct however when I select M1Q only the Opex data for the M3Q is shown, not the capex and revex.

Is there a way to show all the data?

[v-yuezhe-msft · Accepted solution]

@jake88,

What columns do you use to create your visual, when you create a table visual as shown in the following screenshot, the M3Q measure will return correct value even if you select M1Q in the Event slicer.


However, if you add Type column in the table visual, when you select M1Q in the Event slicer, the M3Q measure will be evaluated by the current context and changes to a different value, this is an expected behavior. For more details about context, please review this article: https://powerbi.microsoft.com/en-us/documentation/powerbi-desktop-quickstart-learn-dax-basics/ . If you want to show all data for M3Q, you would need to add two blank rows for M1Q in capex and revex types.



Regards,
Lydia