Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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:
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!!
Solved! Go to Solution.
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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
100 | |
78 | |
64 | |
58 |
User | Count |
---|---|
148 | |
113 | |
97 | |
84 | |
67 |