Forum Discussion
CALCULAR SUBTOTALES EN UNA MATRIZ
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
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.
Best Regards,
KellyDid I answer your question? Mark my post as a solution!
6 Replies
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))
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.
Best Regards,
KellyDid I answer your question? Mark my post as a solution!
