cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
SSS
Helper I
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]))
		);
		CALCULATE(SUM(Consulta1[Cumplimentadas Valor]))
	))

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:

 

Sin título.png

 

 

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:

Sin título2.png

 

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
v-jiascu-msft
Microsoft
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.

View solution in original post

1 REPLY 1
v-jiascu-msft
Microsoft
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.

View solution in original post

Helpful resources

Announcements
2022 Release Wave 1 760x460.png

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.

Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors