cancel
Showing results for
Did you mean:
Helper I

## Compare between measures

Good Morning fellas,

My problem is the following one:

I am representing a measure in a line graph that calculates my Rotation Index.

My data looks like:

FECHA(is the date)         Country          Territory           Cumplimentadas valor (operations made)         Pendiente (pending operations)

...

In order to compute this measure I made the following ones:

```Medida DiaEntradasAcumuladas =
IF(
HASONEVALUE(Consulta1[FECHA]);
SUMX(
FILTER(
ALL(Consulta1[FECHA]);
Consulta1[FECHA] <=VALUES(Consulta1[FECHA])
&& MONTH(Consulta1[FECHA]) = MONTH(VALUES(Consulta1[FECHA]))
);
))```

In the previous measure I am computing the sum of the values in "Cumplimentadas Valor" re-starting the sum at the first day of each month.

`Medida DiaProduccionMediaDiaria = [Medida DiaEntradasAcumuladas] / SUM(ExcelDiasYFestivos[Acumulado Festivo])`

This measure calculates the diary production according the sum of values at each day of the month divided between the productive days acumulated at the day of the calculation (from another table).

`Medida Dia Indice Rotacion = CALCULATE(SUM(Consulta1[Pendiente Valor]))	/[Medida DiaProduccionMediaDiaria]`

Finally, my Rotation Index is calculated by the division between the sum of the values of Pending operations and the measure of the Diary Production.

So I obtain this graphic:

Now the problem, this graphic can be filtered by region, type of service, etc. What I need is also another line in the graphic (the same values) but that cannot be filtered by the region column. So this new line would be always the total values of the country and won't be filtered by the different regions in order to compare the regions with the national value.

How can I do this?

I tried to perform the same operations in a duplicated table and then relation the two tables but the measures are not able to make a relation between the dates, etc of the two tables (as I cannot put two tables in the FILTER part of the 1st measure in SUMX).

In order to make it more clear, It should look something like:

Where the red line is the National Value (cannot be filtered by territory but can be filtered by all the other values like services, date, etc.) and the blue one is the Value that can be filtered by all the slicers (including territory so I can compare the value between the territories and the National one).

Is there any way to perform what I am asking?

Thanks a lot guys!!

1 ACCEPTED SOLUTION
Microsoft

Hi @SSS,

You only need to add a little part to your formula like this. Which measure do you use in your visual? You can do it to all your measures.

```Medida Dia Indice Rotacion =
CALCULATE (
SUM ( Consulta1[Pendiente Valor] ) / [Medida DiaProduccionMediaDiaria],
ALL ( Consulta1[Territory] )  //clear the filter from Consulta1[Territory])```

The function has the similar behavior: Allexcept. If you need more help, please provide a sample? The pbix file would be great.

Best Regards!

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Microsoft

Hi @SSS,

You only need to add a little part to your formula like this. Which measure do you use in your visual? You can do it to all your measures.

```Medida Dia Indice Rotacion =
CALCULATE (
SUM ( Consulta1[Pendiente Valor] ) / [Medida DiaProduccionMediaDiaria],
ALL ( Consulta1[Territory] )  //clear the filter from Consulta1[Territory])```

The function has the similar behavior: Allexcept. If you need more help, please provide a sample? The pbix file would be great.

Best Regards!

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Announcements

#### 2022 Release Wave 1 Plan

Power Platform release plan for the 2022 release wave 1 describes all new features releasing from April 2022 through September 2022.