¿Cómo hacer redondeo personalizado en DAX?

[Syndicate_Admin]

Hola a todos

¡Aquí hay un cracker navideño para nuestros especialistas en DAX!

Quiero crear una medida DAX que calcule el porcentaje de valores por categoría en una tabla al total de esa tabla, con el siguiente redondeo personalizado:

- Todos los porcentajes deben redondearse al entero más cercano.

- Luego hay que identificar cuántos puntos porcentuales faltan para alcanzar el 100% en total.

- A continuación, estos puntos porcentuales deben sumarse cada uno a los porcentajes con la parte decimal más grande en orden descendente.

Tenga en cuenta que el conjunto de datos es enorme, por lo que el impacto del cálculo de DAX debe limitarse tanto como sea posible.

Así, por ejemplo:

Categoría A: 26,98%,
Categoría B: 34,78%,
Categoría C: 38,24%

Redondeado hacia abajo esto se convierte en:

Categoría A: 26
Categoría B: 34
Categoría C: 38
26 + 34 + 38 = 98 -> 2 puntos porcentuales faltantes

Estos puntos porcentuales faltantes deben asignarse a los 2 porcentajes con la parte decimal más alta:
Categoría A: 27
Categoría B: 35
Categoría C: 38
27 + 35 + 38 = 100%

¿Cómo puedo hacer esto con DAX? Creo que se requiere alguna iteración y / o almacenamiento en búfer, pero ¿cómo lo hago?

@parry2k tal vez lo sepas?

[Syndicate_Admin]

@PunchBird bien aquí está, avíseme si tiene alguna pregunta. Podría terminar haciendo un video sobre él y explicaré todo, aunque aún no estoy seguro.

[Syndicate_Admin]

@parry2k gracias, ¡echaré un vistazo y te lo haré saber!

[Syndicate_Admin]

Hola @PunchBird Lo siento, estaba viajando y no pude llegar a usted en el archivo pbix. Avíseme si aún necesita el archivo. ¡¡Bien!!

[Syndicate_Admin]

@parry2k sí, ¡todavía me gustaría echarle un vistazo! Gracias 🙂

[Syndicate_Admin]

@PunchBird versión contraída:

2 - Final Share Short % = 
//select current visible category group
VAR __currentCategory2 = SELECTEDVALUE ( Category[Category2] )
//create base table for the current visible category group
VAR __baseTable= 
ADDCOLUMNS (
    ADDCOLUMNS ( 
        SUMMARIZE ( 
            FILTER ( 
                ALL ( Category ), 
                Category[Category2] = __currentCategory2  
            ), 
            Category[Category2], 
            Category[Category1] 
        ), 
        "@BaseShare", [1 - Base Share %],   --change this measure to the % measure
        "@RoundShare", ROUNDDOWN ( [1 - Base Share %], 2 ) 
    ),
    "@RemainderShare", [@BaseShare] - [@RoundShare] 
)
//get the count of remainder to be distributed
VAR __distributionCount = INT ( SUMX ( __baseTable, [@RemainderShare] * 100 ) )    
//find out to which categories the remainder will be distributed, in other words, what base % will be rounded upwards
VAR __distributionTable = 
    SELECTCOLUMNS ( 
        WINDOW ( 
            1, ABS, 
            __distributionCount, ABS, 
            __baseTable, 
            ORDERBY ( [@RemainderShare], DESC ) 
        ), 
        [Category1], 
        [Category2] 
    )
//round up the share %
VAR __roundUp = 
CALCULATE ( 
    ROUNDUP ( [Sum Value], 0 ), 
    KEEPFILTERS ( 
        TREATAS (  __distributionTable,  Category[Category1], Category[Category2] ) 
    ) 
)
//find result, the one which are not rounded up will be rounded down
RETURN IF ( __roundUp == BLANK (), ROUNDDOWN ( [Sum Value], 0 ), __roundUp )

[Syndicate_Admin]

@parry2k Estoy tratando de reproducir esto, pero me estoy perdiendo un poco... ¿Puede compartir el archivo PBIX al que pertenece esta medida? ¡Muchas gracias!

[Syndicate_Admin]

Wow, eso se ve realmente increíble. Necesito ver tus videos sobre la función WINDOW para entender lo que has hecho, esa va a ser mi próxima tarea en mi lista. ¡Muchas gracias! 🙂

[Syndicate_Admin]

@PunchBird ya tiene una excelente solución de @Greg_Deckler quería probar nuevas funciones de WINDOW y ver si eso ayuda. Tenga en cuenta que se basa en que tiene una tabla de dimensiones de categoría que tiene una relación con la tabla de transacciones .

aquí está la medida DAX, algunos de los pasos se pueden contraer en un solo paso, pero acabo de agregar múltiples variables para mayor claridad y para explicar la lógica detrás de la solución. (Publicaré una versión colapsada pronto)

2 - Final Share % = 
//select current visible category group
VAR __currentCategory2 = SELECTEDVALUE ( Category[Category2] )
//create base table for the current visible category group
VAR __baseTable= 
    ADDCOLUMNS ( 
        SUMMARIZE ( 
            FILTER ( 
                ALL ( Category ), 
                Category[Category2] = __currentCategory2  
            ), 
            Category[Category2], 
            Category[Category1] 
        ), 
        "@BaseShare", [1 - Base Share %]   --change this measure to the % measure
    )
//add a column to round down base %
VAR __roundTable = 
    ADDCOLUMNS ( 
        __baseTable, 
        "@RoundShare", ROUNDDOWN ( [@BaseShare], 2 ) 
    )
//add a column to difference between base share and round down share %
VAR __remainderTable = 
    ADDCOLUMNS (
        __roundTable,
         "@RemainderShare", [@BaseShare] - [@RoundShare] 
    )
//get the count of remainder to be distributed
VAR __distributionCount = INT ( SUMX ( __remainderTable, [@RemainderShare] * 100 ) )    
//find out to which categories the remainder will be distributed, in other words, what base % will be rounded upwards
VAR __distributionTable = 
    SELECTCOLUMNS ( 
        WINDOW ( 
            1, ABS, 
            __distributionCount, ABS, 
            __remainderTable, 
            ORDERBY ( [@RemainderShare], DESC ) 
        ), 
        [Category1], 
        [Category2] 
    )
//round up the share %
VAR __roundUp = 
CALCULATE ( 
    ROUNDUP ( [Sum Value], 0 ), 
    KEEPFILTERS ( 
        TREATAS (  __distributionTable,  Category[Category1], Category[Category2] ) 
    ) 
)
//find result, the one which are not rounded up will be rounded down
RETURN IF ( __roundUp == BLANK (), ROUNDDOWN ( [Sum Value], 0 ), __roundUp )

Además, si está interesado, consulte la lista de reproducción completa en mi canal de youtube para conocer las nuevas funciones de WINDOWS DAX. https://youtube.com/playlist?list=PLiYSIjh4cEx0BDzmo48YIPzw_dIC0Kd95

[Syndicate_Admin]

@Greg_Deckler @ppm1 @parry2k gracias por sus cálculos y aportes, ¡ambos parecen funcionar! Sin embargo, me acabo de dar cuenta de que no tengo una, sino dos categorías, cada una de las cuales se puede filtrar por separado. Los porcentajes de Categoría2 conforman un total del 100%

Así que mi mesa se ve algo así:

Categoría1	Categoría2	Porcentaje
A	X	43.75
B	X	12.50
C	X	12.50
D	X	31.25
A	Y	40.38
B	Y	5.77
C	Y	23.08
D	Y	30.77

Cada Categoría2 redondeada hacia abajo se convierte en:

Categoría1	Categoría2	Porcentaje
A	X	43
B	X	12
C	X	12
D	X	31
A	Y	40
B	Y	5
C	Y	23
D	Y	30

Categoría2 X -> 2 puntos porcentuales faltantes

Categoría2 Y -> 2 puntos porcentuales faltantes

Estos puntos porcentuales faltantes deben asignarse a los 2 porcentajes con la parte decimal más alta. Tenga en cuenta que hay un empate en X de la Categoría 2, por lo que un punto porcentual va a la primera. Supongo que debe ser bastante simple de agregar a los cálculos que proporcionó, pero no puedo lograr que funcione ... ¿Alguna idea? ¡Muchas gracias de antemano!

Categoría1	Categoría2	Porcentaje redondeado
A	X	44
B	X	13
C	X	12
D	X	31
A	Y	40
B	Y	6
C	Y	23
D	Y	31

[Syndicate_Admin]

@PunchBird He modificado las cosas para que estén más en línea con sus datos y requisitos. Vea si esto funciona. El PBIX actualizado se adjunta debajo de la firma.

Measure 2 = 
    VAR __Cat2 = MAX('Table'[Category2])
    VAR __Table = 
        GENERATE(
            SUMMARIZE(
                FILTER(ALLSELECTED('Table'), [Category2] = __Cat2),
                'Table'[Category1],'Table'[Category2],"Value",MAX([Percentage])),
                VAR __Value = [Value]
                VAR __RD = ROUNDDOWN(__Value,0)
                VAR __Decimal = __Value - __RD
            RETURN
                ROW(
                    "RD", __RD,
                    "Decimal", __Decimal
                )
        )
    VAR __MaxDecimal = MAXX(__Table,[Decimal])
    VAR __MaxCategory = MAXX(FILTER(__Table, [Decimal] = __MaxDecimal),[Category1])
    VAR __2ndMaxDecimal = MAXX(FILTER(__Table, [Category1] <> __MaxCategory), [Decimal])
    VAR __2ndMaxCategory = MAXX(FILTER(__Table, [Category1] <> __MaxCategory && [Decimal] = __2ndMaxDecimal),[Category1])
    VAR __Category = MAX('Table'[Category1])
    VAR __Result = 
        IF(
            __Category = __MaxCategory || __Category = __2ndMaxCategory, 
            ROUNDUP(MAX([Percentage]),0), 
            ROUNDDOWN(MAX([Percentage]),0)
        )
RETURN
    __Result //CONCATENATEX(__Table, [Category1]&":"&[Category2]&":"&[Value]&":"&[RD]&":"&[Decimal],UNICHAR(10)&UNICHAR(13))

[Syndicate_Admin]

¡Brillante, funciona, también en mi conjunto de datos "real"! Muchas gracias @Greg_Deckler , ¡eres una estrella! 🙂

Y también gracias a todos los demás colaboradores @bolfri @ppm1 @parry2k , me ayudaron a dar forma a mis pensamientos.

[Syndicate_Admin]

@Greg_Deckler vaya, noté un error: parece que el resultado ahora siempre redondea dos valores, incluso si solo se debería haber redondeado un valor, vea el ejemplo a continuación para la categoría Z ... que debería haber sido 50 + 24 + 16 + 10. ¿Alguna idea sobre esto?

[Syndicate_Admin]

@PunchBird Solución fácil para eso, vea a continuación y PBIX adjunto.

Measure 2 = 
    VAR __Cat2 = MAX('Table'[Category2])
    VAR __Table = 
        GENERATE(
            SUMMARIZE(
                FILTER(ALLSELECTED('Table'), [Category2] = __Cat2),
                'Table'[Category1],'Table'[Category2],"Value",MAX([Percentage])),
                VAR __Value = [Value]
                VAR __RD = ROUNDDOWN(__Value,0)
                VAR __Decimal = __Value - __RD
            RETURN
                ROW(
                    "RD", __RD,
                    "Decimal", __Decimal
                )
        )
    VAR __MaxDecimal = MAXX(__Table,[Decimal])
    VAR __MaxCategory = MAXX(FILTER(__Table, [Decimal] = __MaxDecimal),[Category1])
    VAR __2ndMaxDecimal = MAXX(FILTER(__Table, [Category1] <> __MaxCategory), [Decimal])
    VAR __2ndMaxCategory = MAXX(FILTER(__Table, [Category1] <> __MaxCategory && [Decimal] = __2ndMaxDecimal),[Category1])
    VAR __Category = MAX('Table'[Category1])
    VAR __SumDown = SUMX(__Table, [RD])
    VAR __Result = 
        SWITCH(__SumDown,
            99, 
                IF(
                    __Category = __MaxCategory, 
                    ROUNDUP(MAX([Percentage]),0), 
                    ROUNDDOWN(MAX([Percentage]),0)
                ),
            98, 
                IF(
                    __Category = __MaxCategory || __Category = __2ndMaxCategory, 
                    ROUNDUP(MAX([Percentage]),0), 
                    ROUNDDOWN(MAX([Percentage]),0)
                )
        )
RETURN
    __Result //CONCATENATEX(__Table, [Category1]&":"&[Category2]&":"&[Value]&":"&[RD]&":"&[Decimal],UNICHAR(10)&UNICHAR(13))

[Syndicate_Admin · Accepted solution]

¡Genial, eso funcionó! Muchas gracias, muy apreciado 🙂

Y agregué una alternativa para SWITCH en caso de que no sea necesario redondear nada (en el improbable caso de que todos sean números con 0 decimales)

[Syndicate_Admin]

Hola

Creo que estás tratando de hacerlo mal. No cree medidas demasiado complejas que no sean nessesery. 😄

En Power Query M:

Agregue una columna Percentage_to_number que sea su porcentaje original dividido por 100 y haga que esto sea un número (con decimales)

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUYoAYhNjPXNTpVidaCUnqIihkZ6pAVjEGUPEBSpibKhnBNEFMicSZI6BnrEF3ByQiKmeuTncGJCAkbGegQXcGJCIsQFYTSwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Category1 = _t, Category2 = _t, Percentage = _t]),
    #"Replaced Value" = Table.ReplaceValue(Source,".",",",Replacer.ReplaceText,{"Percentage"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Replaced Value",{{"Category1", type text}, {"Category2", type text}, {"Percentage", type number}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Percentage_to_number", each [Percentage] / 100),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Percentage_to_number", type number}})
in
    #"Changed Type1"

En DAX:

Cree una medida de porcentaje y cambie el formato a porcentaje:

Percentage measure = SUM('Sample'[Percentage_to_number])

Como puedes ver es casi lo mismo que el anterior.

Para deshacerse de los decimales simplemente cámbielo a 0.

Efecto final:

Los números son correctos y se calculan por ley 🙂 matemática

[Syndicate_Admin]

¡Muchas gracias! Esta habría sido una gran solución... pero mira los resultados de la medida porcentual de la categoría X ... 44 + 13 + 13 + 31 = 101 !

El empate (y creo que dos valores cualesquiera con ,5 decimales en una categoría) parece estropear el total ... de ahí la necesidad de redondeo personalizado. Déjame saber tus pensamientos sobre cómo resolver esto, gracias 🙂

[Syndicate_Admin]

Entiendo su punto. Dijiste que quieres redondear hacia abajo, así que ...

En los datos originales, agregue una nueva columna DAX:

Redondeo = REDONDEO ('Muestra'[Porcentaje],0)

A continuación , agregue una nueva columna

Nuevo porcentaje =
var numerator= [Redondeo]
var denominator= CALCULATE(SUMA('Muestra'[Redondeo]),ALLEXCEPT('Muestra','Muestra'[Categoría2]))
return DIVIDE(numerador,denominador)

Resultado en tabla:

Como puede ver, el 2% faltante se dividió entre la Categoría A y D.

Pero podemos usar RoundingUp para dar este 2% faltante a la categoría B y C.

Otra vez. Agregue una nueva columna:

RedondeoArriba = REDONDEO([Porcentaje],0)

Y otra nueva columna:

Nuevo porcentaje UP =
var numerator= [Redondeo]
var denominator= CALCULATE(SUMA('Muestra'[Redondeo]),ALLEXCEPT('Muestra','Muestra'[Categoría2]))
return DIVIDE(numerador,denominador)

Resultado en tabla:

Según la ruta que elijas, obtendrás diferentes resultados.

Elige el que prefieras.

[Syndicate_Admin]

@bolfri gracias por sus sugerencias, pero me gustaría evitar agregar columnas adicionales, porque esto aumentará el tamaño del modelo e impactará dramáticamente el rendimiento (la tabla de hechos tiene más de un millón de filas). Además, el requisito es que los puntos porcentuales restantes deben sumarse a los valores con el decimal más grande, en orden descendente...

[Syndicate_Admin]

¿Quieres cambiar el valor? Si las categorías 2 y 3 tienen el mismo valor, ¿por qué quieres darles un impacto porcentual diferente? 😄 Puedes hacerlo en un solo paso o por una medida. Fue solo un ejemplo de los resultados. Si puede aceptar eso (por ejemplo, esta solución de redondeo), puedo darle pasos de Power Query M para cambiarlo en la fuente.

[Syndicate_Admin]

@bolfrisu comentario tiene sentido, pero desafortunadamente no se me permite desviarme de los requisitos re. la distribución de los puntos porcentuales. Definitivamente tengo curiosidad por ver cómo se vería 🙂 un paso de Power Query M para esto