Compare actual and target for measures across different tables

[SamVH12]

Hi guys,

i have a model like this

I create 6 separate measures for each Login, Value, Quant

But i dont know how can I group them together to compare Target and Actual categories, like in the chart and table shown below? 

I don’t want to change anything in the fact or dimension tables. Do we have a way to work on this kind of chart? Any help is appreciated.

Thank you in advance!

[mdaatifraza5556 · Accepted solution]

Hi @SamVH12 

Can you please follow the below steps to get your result.

1. Create a table using below dax.
       

CategoryTable =

DATATABLE(

    "Category", STRING,

    {

        {"Login"},

        {"Quantity"},

        {"Value"}

    }

)

2. Now create two measure for the results.

First measure

Selected Actual =

SWITCH(

    SELECTEDVALUE(CategoryTable[Category]),

    "Login", [Actual_login],

    "Quantity", [Actual_Quant],

    "Value", [Actual_values]

)

2nd measure

Selected Target =

SWITCH(

    SELECTEDVALUE(CategoryTable[Category]),

    "Login", [Target_login],

    "Quantity", [Target_Quant],

    "Value", [Target_value]

)

3. Now drag the category from category table on x axis.

and the above  2 measures on y axis

Selected Actual and 

Selected Target 

If this answers your questions, kindly accept it as a solution and give kudos.

[SamVH12]

Yay, it works well for me! Many thanks for your help! 🤗

[freginier]

Create a calculated field called KPI Group

CASE [Measure Names]
WHEN 'Login_Actual' THEN 'Login'
WHEN 'Login_Target' THEN 'Login'
WHEN 'Value_Actual' THEN 'Value'
WHEN 'Value_Target' THEN 'Value'
WHEN 'Quant_Actual' THEN 'Quant'
WHEN 'Quant_Target' THEN 'Quant'
END

Create another calculated field called Actual/Target Category

CASE [Measure Names]
WHEN 'Login_Actual' THEN 'Actual'
WHEN 'Login_Target' THEN 'Target'
WHEN 'Value_Actual' THEN 'Actual'
WHEN 'Value_Target' THEN 'Target'
WHEN 'Quant_Actual' THEN 'Actual'
WHEN 'Quant_Target' THEN 'Target'
END

Then in the visual : 

• Drag KPI Group to the Rows shelf.
• Drag Actual/Target Category to the Columns shelf.
• Drag Measure Values to the Text (or Label) shelf.
• On the Measure Values card, keep only your six measures.

[SamVH12]

Thank you for your help. I'm not sure I understand the 'calculated field', does it refer to calculated columns? If so, the 6 measures (Value, Quant, Login for both target and actual) are in 4 different tables. How can we create calculated columns using all six of them?

[burakkaragoz]

Hi @SamVH12 ,

You can definitely do this without changing your fact or dimension tables. The key is to reshape your measures into a unified format using a disconnected table and SWITCH() logic.

Here’s how:

1. Create a disconnected table with the categories you want to compare:

   MeasureType = DATATABLE("Type", STRING, {{"Actual Login"}, {"Actual Value"}, {"Actual Quant"}, {"Target Login"}, {"Target Value"}, {"Target Quant"}})

1. Create a single measure that switches based on the selected type:

   SelectedMeasure =
   SWITCH(
       SELECTEDVALUE(MeasureType[Type]),
       "Actual Login", [Actual Login],
       "Actual Value", [Actual Value],
       "Actual Quant", [Actual Quant],
       "Target Login", [Target Login],
       "Target Value", [Target Value],
       "Target Quant", [Target Quant]
   )

1. Use this in your visual
   Add MeasureType[Type] to the axis and SelectedMeasure as the value. This will group and compare all your actual vs target measures in one chart or table.

This approach keeps your model clean and gives you full flexibility in visuals.

If my response resolved your query, kindly mark it as the Accepted Solution to assist others. Additionally, I would be grateful for a 'Kudos' if you found my response helpful.

[SamVH12]

Thank you, but this will still display 6 measures separately, each with a different label, rather than grouping them by Value, Quant, or Login, and compares them based on actual & target values, which doesn't align with my requirements