Lock a measure value to prevent further calculations

[coriben]

Hello there, I'm having some problems with DAX.

Consider this visual:

It gets it's values from these measures:

Valor Médio de Consulta = DIVIDE([Valor Total de Consultas];'M Consultas'[Consultas Geral];BLANK())

Valor Total de Consultas = CALCULATE(
SUM('0 - UTILIZAÇÃO'[Valor]);
'0 - UTILIZAÇÃO'[Tipo de Guia] = "C"
)

Consultas Geral = CALCULATE(
sum('0 - UTILIZAÇÃO'[Quantidade]);
FILTER('0 - UTILIZAÇÃO';'0 - UTILIZAÇÃO'[Tipo de Guia] = "C")
)

Basically, the chart shows the average value (Valor Médio de Consultas) for appointments, by dividing the overall value of appointments (Valor Total de Consultas) by the quantity of appointments (Consultas Geral). It's working as intended. The problem lies in the legend.

The legend is basically executing the average value measure for each type of appointment. That means it divides the total value of each type of appointment by the total quantity of each type of appointment. That is the logical thing to do, but due to management requirements, we need the average to be calculated based in the total quantity of the two main types of apointment only (I even created a measure for them, they're the bars in the X axis). The goal of this is to show cost distribution in the "Atendimento em Pronto Socorro" type of appointment. The total quantities of each type of appointment should by ignored.  That means that in the bar that is subdivided, each section should do the following calculation:

section avg = Total value of appointment type / Total quantity of "Atendimento em Pronto Socorro" only

I can't find a way to tell DAX that the "Total quantity of "Atendimento em Pronto Socorro" is a fixed value, it keeps calculating the measure for each type of appointment.

Is there a way to do this?

[v-frfei-msft]

Hi @coriben,

Please try to update your formulas to:

Valor Total de Consultas = CALCULATE(
SUM('0 - UTILIZAÇÃO'[Valor]);
FILTER(ALL('0 - UTILIZAÇÃO'),'0 - UTILIZAÇÃO'[Tipo de Guia] = "C"
))

Consultas Geral = CALCULATE(
sum('0 - UTILIZAÇÃO'[Quantidade]),
FILTER(ALL('0 - UTILIZAÇÃO'),'0 - UTILIZAÇÃO'[Tipo de Guia] = "C")
))

If it doesn't meet your requirement, kindly share your sample data and excpted result to me.

Regards,

Frank

[v-frfei-msft]

Hi @coriben,

Does that make sense? If so, kindly mark my answer as a solution to close the case.

Regards,
Frank

[coriben · Accepted solution]

Thanks for your input and sorry for the late response.

No, your solution did not work because ALL() removes all filters, including the sync slicers that were applied by default. That was not the intended result.

Anyway, for future readers, the only way I found was to create individual measures for each average and group them together in the "Value" field of a bar chart. Not the best solution, but it worked.

Thanks!