Porcentaje de totales

[Tesy]

Hola todo@s,

Tengo una tabla en excel en el cual tengo un conteo y al final me da la sumatoria, necesito sacar el Promedio por columna del total de la matriz, lo tengo en excel y necesito replicarlo en Power BI

Necesito sacar la linea de PROMEDIO, los valores en las columnas son conteos.

el resultado es por ejemplo: 1,260 / 5,918= 21.3%

                                               3,290/ 5,918 = 55.6%

de antemano gracias !

[DataNinja777 · Accepted solution]

Hi @Tesy ,

To calculate the average row in Power BI while ensuring that the totals are consistent and unaffected by any filters, you can use the ALL function in your DAX formulas.

First, create a measure for the overall total by using the ALL function. This function removes all filters from the table, ensuring that the total represents the sum of the entire dataset. The measure can be written as:

Total General = SUMX(ALL('Table'), 'Table'[Count])

Next, create another measure to calculate the percentage for each column. This measure divides the sum of the column values by the overall total. The formula is:

Average = DIVIDE(SUM('Table'[Count]), [Total General])

Add these measures to a Matrix visualization in Power BI. Use the column names from your dataset as columns in the matrix, and add the Average measure as a value. This setup will calculate the percentage for each column relative to the unfiltered total of the matrix.

For example, if the total values in the dataset are 1,260 for Column A and 3,290 for Column B, and the grand total is 5,918, the calculations will result in 21.3% for Column A and 55.6% for Column B. The ALL function ensures that the denominator, represented by the Total General measure, reflects the entire dataset, ignoring any filters or slicers applied.

This approach guarantees accurate calculations for the percentages in the matrix and ensures that your average row appears correctly as the sum of the unfiltered data. Let me know if you need further assistance!

Best regards,

[Tesy]

De hecho, junte las 2 medidas en una quedando de la siguiente manera:

Promedio = DIVIDE ( SUM (sheet1[Número de la orden de compra]),

SUMX ( ALL (sheet1), sheet1[Número de la orden de compra] ) )

[Tesy]

Muchisimas gracias, si me dió el resultado esperado, Gracias !!!