Forum Discussion

Syndicate_Admin's avatar
Syndicate_Admin
Icon for Administrator rankAdministrator
2 years ago

Help with splitting in DAX

Nice day
I'm trying to make a split in DAX to get the proportional percentage based on a total, but when I want to make the trade the expected result doesn't come out 

Medida 2 = 
/*Variables para seleccionar el idiona*/
VAR Seleccion = SELECTEDVALUE(IDIOMAS[Idioma])
/*Variables para encontrar el ultimo registro en base a la columna de fecha */
var _lastDateDips = MAXX(FILTER(Industrial_Latam,NOT(ISBLANK(Industrial_Latam[Espacio Disponible (m²)]))),Industrial_Latam[Fecha])
/*Varibles donde se guarda el ultimo valor ecncontrado*/
var Total = SUMX(FILTER(Industrial_Latam,Industrial_Latam[Fecha]=_lastDateDips),(Industrial_Latam[Espacio Disponible (m²)])) 
var Porcentaje = SUMX(Industrial_Latam,SELECTEDVALUE(Industrial_Latam[Espacio Disponible (m²)]))/Total
var Percentage_ALLSELECTED =
DIVIDE (
    SELECTEDVALUE(Industrial_Latam[Espacio Disponible (m²)]),Total
)
return Percentage_ALLSELECTED

The DIVIDE part is where I want to get the proportional percentage of each record, but it always marks me 100%

What I would like to do is have it divide over the total of "Measure" which would be 253,746, if I add the manual value if it gives me the desired result 

Medida 2 = 
/*Variables para seleccionar el idiona*/
VAR Seleccion = SELECTEDVALUE(IDIOMAS[Idioma])
/*Variables para encontrar el ultimo registro en base a la columna de fecha */
var _lastDateDips = MAXX(FILTER(Industrial_Latam,NOT(ISBLANK(Industrial_Latam[Espacio Disponible (m²)]))),Industrial_Latam[Fecha])
/*Varibles donde se guarda el ultimo valor ecncontrado*/
var Total = SUMX(FILTER(Industrial_Latam,Industrial_Latam[Fecha]=_lastDateDips),(Industrial_Latam[Espacio Disponible (m²)])) 
var Porcentaje = SUMX(Industrial_Latam,SELECTEDVALUE(Industrial_Latam[Espacio Disponible (m²)]))/Total
var Percentage_ALLSELECTED =
DIVIDE (
    SELECTEDVALUE(Industrial_Latam[Espacio Disponible (m²)]),253746
)
return Percentage_ALLSELECTED

But I'd like to know if I can save the value of the "Total" variable as something fixed or if it only takes that value when I do the division

3 Replies

  • AlexisOlson's avatar
    AlexisOlson
    Icon for Super User rankSuper User
    2 years ago

    In the definition of VAR Total, the table Industrial_Latam that you're filtering is not the entire table. It's the portion of that table within the local filter context. In particular, it's filtered by Ciudad just like the numerator is.

     

    Try using ALLSELECTED like your last variable implies you would.

     

    ...
VAR Total =
    SUMX (
        FILTER (
            ALLSELECTED ( Industrial_Latam ),
            Industrial_Latam[Fecha] = _lastDateDips
        ),
        Industrial_Latam[Espacio Disponible (m²)]
    )
...

     

  • Syndicate_Admin's avatar
    Syndicate_Admin
    Icon for Administrator rankAdministrator
    2 years ago

    It served me perfectly, if you give me the expected percentages.
    But when I put the information in a table it doesn't give me the sum of the percentages that would be 100%, is there a way to do it?

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi Syndicate_Admin ,

     

    Please this measure.

    Medida 2 = 
/*Variables para seleccionar el idiona*/
VAR Seleccion = SELECTEDVALUE(IDIOMAS[Idioma])
/*Variables para encontrar el ultimo registro en base a la columna de fecha */
var _lastDateDips = MAXX(FILTER(Industrial_Latam,NOT(ISBLANK(Industrial_Latam[Espacio Disponible (m²)]))),Industrial_Latam[Fecha])
/*Varibles donde se guarda el ultimo valor ecncontrado*/
var Total = SUMX(FILTER(ALL(Industrial_Latam),Industrial_Latam[Fecha]=_lastDateDips),(Industrial_Latam[Espacio Disponible (m²)])) 
var Porcentaje = SUMX(Industrial_Latam,SELECTEDVALUE(Industrial_Latam[Espacio Disponible (m²)]))/Total
var Percentage_ALLSELECTED =
DIVIDE (
    SELECTEDVALUE(Industrial_Latam[Espacio Disponible (m²)]),Total
)
return Percentage_ALLSELECTED

    Or you can create a separate total measure.

    Total measure = 

var _lastDateDips = MAXX(FILTER(Industrial_Latam,NOT(ISBLANK(Industrial_Latam[Espacio Disponible (m²)]))),Industrial_Latam[Fecha])

var Total = SUMX(FILTER(ALL(Industrial_Latam),Industrial_Latam[Fecha]=_lastDateDips),(Industrial_Latam[Espacio Disponible (m²)])) 

return Total
    Medida 2 = 

var Percentage_ALLSELECTED =
DIVIDE (
    SELECTEDVALUE(Industrial_Latam[Espacio Disponible (m²)]),[Total measure]
)
return Percentage_ALLSELECTED

    If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.

     

    Best Regards,

    Neeko Tang

    If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly.