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.
Hola buen día colegas. Quizás me pueden ayudar con lo siguiente:
Tengo una matriz en Power BI que tiene el campo Municipio en ROW y en Values tiene tres campos: 1.- Personas, 2.- % que es una medida del % de personas sobre el total y 3.- Color que es otra medida que se calcula de acuerdo a la Medida % ( Mayor o igual a 9% pintar la fila en RED, de 5 a 8.99 en YELLOW y menor a 5 GREEN.
Ahora necesito tener los subtotales por cada grupo de color y obtener un acumulado de %. La tabla final debe verse así:
¿ Alguien que me pueda ayudar a concretizar esta solicitud?
Gracias
Solved! Go to Solution.
Hi @Macesa ,
I'll show you a workaround to do it:
First create a table such as below:
Putting all the fields of column "Municipio" with nu3 rows of "subtotal" in it ,adding an index column in query editor,then modify your measure as below:
Measure 2 =
IF (
ISINSCOPE ( 'Table (3)'[Column1] ),
IF (
FIND ( "total", SELECTEDVALUE ( 'Table (3)'[Column1] ), 1, -1 ) > 0,
VAR currentTotalIndex =
MIN ( 'Table (3)'[Index] )
VAR lastTotalIndex =
CALCULATE (
MAX ( 'Table (3)'[Index] ),
FILTER (
ALLSELECTED ( 'Table (3)' ),
'Table (3)'[Index] < currentTotalIndex
&& FIND ( "total", [Column1], 1, -1 ) > 0
)
) + 0
RETURN
CALCULATE (
SUM ( 'Table (3)'[value] ),
FILTER (
ALLSELECTED ( 'Table (3)' ),
'Table (3)'[Index] > lastTotalIndex
&& 'Table (3)'[Index] < currentTotalIndex
)
),
SUM ( 'Table (3)'[value] )
),
CALCULATE (
SUM ( 'Table (3)'[value] ),
FILTER ( 'Table (3)', FIND ( "total", [Column1], 1, -1 ) = -1 )
)
)
Finally you will see:
Here is the related .pbix file.
y cómo se podría hacer para tener una fila de año total y TDY debajo,
Necesito debajo del total para agregar el total YTD, no sé cómo poner los totales en fila, debajo de cada columna su respectivo total YTD ni cómo calcular el YTD.
Hi @Macesa ,
I'll show you a workaround to do it:
First create a table such as below:
Putting all the fields of column "Municipio" with nu3 rows of "subtotal" in it ,adding an index column in query editor,then modify your measure as below:
Measure 2 =
IF (
ISINSCOPE ( 'Table (3)'[Column1] ),
IF (
FIND ( "total", SELECTEDVALUE ( 'Table (3)'[Column1] ), 1, -1 ) > 0,
VAR currentTotalIndex =
MIN ( 'Table (3)'[Index] )
VAR lastTotalIndex =
CALCULATE (
MAX ( 'Table (3)'[Index] ),
FILTER (
ALLSELECTED ( 'Table (3)' ),
'Table (3)'[Index] < currentTotalIndex
&& FIND ( "total", [Column1], 1, -1 ) > 0
)
) + 0
RETURN
CALCULATE (
SUM ( 'Table (3)'[value] ),
FILTER (
ALLSELECTED ( 'Table (3)' ),
'Table (3)'[Index] > lastTotalIndex
&& 'Table (3)'[Index] < currentTotalIndex
)
),
SUM ( 'Table (3)'[value] )
),
CALCULATE (
SUM ( 'Table (3)'[value] ),
FILTER ( 'Table (3)', FIND ( "total", [Column1], 1, -1 ) = -1 )
)
)
Finally you will see:
Here is the related .pbix file.
In Quick measure, you have an option for running total, create a running total using that, Say Cumm
https://www.edureka.co/community/41446/how-to-calculate-cumulative-total-and-in-dax
Create GT Like
Calculate(sum(Table[Personas]),all(Table))
% of Cumm = [Cumm]/GT
https://www.c-sharpcorner.com/article/calculate-cumulativerunning-total-in-power-bi/
Create a color measure.
color =
var _change =[% of Cumm]
return
SWITCH (
TRUE(),
_change > 80, "green",
_change > 38, "yellow",
"red"
)
No go to conditional formatting or background color of the field and DO it for each field. Go to advance control and select field and choose this measure.
https://docs.microsoft.com/en-us/power-bi/desktop-conditional-table-formatting#color-by-color-values
Hola, la matriz está completada con las medidas de colores.
Ahora lo que quiero hacer es calcular los subtotales para cada rango de color ya que no he encotrando forma de hacerlo en power bi.
Así se mira actualmente la matriz.
As of now, I have no Idea about how color is working, but a measure like should give a total of color
red = calculate(sum(table[persona]), values(table[MUNICIPO]),filter(table, [Cumm%]<=.38))
or
sumx(filter(summarize(table,table[MUNICIPO],"_cumm",[Cumm%],"_per",sum(table[persona])),[_cumm]<=.38),[_per])
green= calculate(sum(table[persona]),values(table[MUNICIPO]), filter(table, [Cumm%]>=.78))
@Macesa , I am not sure, If the last update help you or you need more info.
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 |
---|---|
97 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
126 | |
106 | |
105 | |
86 | |
72 |