Fact and Dim table shows different values when used in power bi visual, how to fix it?

[sumiteshkumar]

I have a table "forecast" in which i have a calculated measure "PerformanceMeasure", i am trying to use this measure in donut chart, but it does not allow me to drag and place in legends place in donut chart,

PerformanceMeasure =
SWITCH(
TRUE(),
[Forecast vs Sold At] >= -0.03, "On Track",
AND([Forecast vs Sold At] < -0.03, [Forecast vs Sold At] >= -0.06), "At Risk",
[Forecast vs Sold At] < -0.06, "Off Track"
)

here "Forecast vs Sold At" is also a calc measure from which i have created a new measure "PerformanceMeasure"

as "PerformanceMeasure" does not allow me to drag and place in legends place in donut chart, so i created a dim table from fact table "forecast"

PerformanceSummary =
SUMMARIZE(
forecast,
forecast[ID],
"Performance Status", [PerformanceMeasure],
"Project Count", COUNT(forecast[ID])
)

now when i compare "Performance Status" and "PerformanceMeasure", "Performance Status" shows different values

[Bibiano_Geraldo]

Hi @sumiteshkumar ,

Unfortunately, measures cannot be directly dragged into the Legend field of a donut chart. This is because the Legend field is designed to work with categorical data, which typically comes from columns containing distinct values or categories. Measures, on the other hand, represent aggregated or calculated values, which do not inherently provide the categorical grouping required for the Legend.

To work around this, you could create a calculated column or use an existing column that represents the desired categories for your Legend. This way, the chart can effectively group and display the data as intended. Let me know if you'd like further guidance on this!

[sumiteshkumar]

@Bibiano_Geraldo the "PerformanceMeasure" shows correct result, how do i use the same as legend. 

If you can help mw step by step!

[Bibiano_Geraldo]

Hi @sumiteshkumar ,
Create a new calculated with the bellow DAX:

PerformanceMeasure = 
VAR vForecast = //paste here the calculation of "Forecast vs Sold At" DAX, do not refer measures here, the calculated columns evaluate each row individually.

RETURN
SWITCH(
    TRUE(),
    'vForecast >= -0.03, "On Track",
    AND(vForecast < -0.03, vForecast >= -0.06), "At Risk",
    vForecast < -0.06, "Off Track"
)

 Let me know if you need any further assistance!

[sumiteshkumar]

@Bibiano_Geraldo  i created the DAX, it works fine with visuals but i tried using the same in visual but i am not able to drag in value of the same in donut chart, i tried creating the calculated column from the same DAX, i was able to drag the same in value of donut chart but chart goes blank.

[Bibiano_Geraldo]

Hi,

It's possible to share the sample file with no sensitive data? 

[sumiteshkumar]

@Bibiano_Geraldo that will be difficult, as it contains sensetive data.

For your understanding i have shared the measure that i have created.

1) measure to calculate: Forecast vs Sold At % = forecast[Forecast | Gross Profit %]-sold at[Sold At | Gross Profit %]

2) measure to calculate: PerformanceMeasure =
SWITCH(
TRUE(),
[Forecast vs Sold At %] >= -0.03000, "On Track",
AND([Forecast vs Sold At %] < -0.03001, [Forecast vs Sold At %] >= -0.06000), "At Risk",
[Forecast vs Sold At %] < -0.06001, "Off Track"
)

3) measure to calulate: Performance =
VAR vForecast = Forecast vs Sold At % = forecast[Forecast | Gross Profit %]-sold at[Sold At | Gross Profit %]
RETURN
SWITCH(
TRUE(),
vForecast >= -0.03, "On Track",
AND(vForecast < -0.03, vForecast >= -0.06), "At Risk",
vForecast < -0.06, "Off Track"
)

The last measure does not allowed me to drag it in the donut values but it does not worked, so i created a column that allowed me drag and drop but it shows blank.

[danextian]

Hi @sumiteshkumar 

While you can create a calculated table using a measure, that doesn't mean that it will retain whatever relationship it has to the tables and columns it references after calculated table creation. So essentially what you've done is add non-related dimensions and a measure to a viz.

[danextian]

Measures cannot be used as legends as they dont have a row context and are evaluated based on the other dimensions in the viz. If you want to mimic this behavious, you can use a disconnected table  that holds the legend values and then a measure  to return values based on measure categorization.  Please see the attached pbix.

[sumiteshkumar]

@danextian In my case "PerformanceMeasure" shows correct result. I tried creating the new table with Query, used "On Track", "Off Track", "At Risk" as you created in the pbix, but i shows incorrect data.