The total of the measurement is not shown

[Yeimy]

Hello 

I have the following dax, wich divides my precriptions between the total prescriptions, this according to the hierarchy level of my matrix. The calculations are fine, the percentages are correct for all levels of hierarchy 

My problem is that the total is not being shown at the end, wich sould be 100%

MS Tam2 Mercado V_Mercado = IF(ISINSCOPE(DIM_MEDICO[NOMBRE_MEDICO]),

    DIVIDE([Prescripciones Tam],[Prescripciones Tam MEDICO2 Total V_mercado]),

        IF(ISINSCOPE(DIM_PRODUCTO[NOMBRE_PRODUCTO]),DIVIDE([Prescripciones Tam],[Prescripciones Tam2 Producto Total V_Mercado]),

            IF(ISINSCOPE(DIM_PRODUCTO[MARCA]),DIVIDE([Prescripciones Tam],[Prescripciones Tam2 Marca Total V_Mercado]),

            IF(ISINSCOPE(MEDICO_REPRESENTANTE[NOMBRE_REPRESENTANTE]),

    DIVIDE([Prescripciones Tam],[Prescripciones Tam2 Representante Total V_Mercado])

    )

        )

        )

)


or 

MS Tam2 Mercado V_Mercado = IF(ISINSCOPE(DIM_MEDICO[NOMBRE_MEDICO]),

    DIVIDE([Prescripciones Tam],[Prescripciones Tam MEDICO2 Total V_mercado]),

        IF(ISINSCOPE(DIM_PRODUCTO[NOMBRE_PRODUCTO]),DIVIDE([Prescripciones Tam],[Prescripciones Tam2 Producto Total V_Mercado]),

            IF(ISINSCOPE(DIM_PRODUCTO[MARCA]),DIVIDE([Prescripciones Tam],[Prescripciones Tam2 Marca Total V_Mercado]),

     DIVIDE([Prescripciones Tam],[Prescripciones Tam2 Representante Total V_Mercado])

    )

        )

)

[123abc · Accepted solution]

It seems like you're trying to create a DAX measure that calculates percentages based on different levels of hierarchy in your matrix. However, you're encountering an issue where the total percentage is not being displayed as 100%.

To ensure that the total percentage is displayed correctly as 100%, you need to adjust your DAX measure to handle the total level properly. You should calculate the total percentage separately and then include it in your measure.

Here's how you can modify your DAX measure to include the total percentage:

MS Tam2 Mercado V_Mercado =
VAR TotalPrescriptions =
SWITCH (
TRUE (),
ISINSCOPE ( DIM_MEDICO[NOMBRE_MEDICO] ), [Prescripciones Tam MEDICO2 Total V_mercado],
ISINSCOPE ( DIM_PRODUCTO[NOMBRE_PRODUCTO] ), [Prescripciones Tam2 Producto Total V_Mercado],
ISINSCOPE ( DIM_PRODUCTO[MARCA] ), [Prescripciones Tam2 Marca Total V_Mercado],
ISINSCOPE ( MEDICO_REPRESENTANTE[NOMBRE_REPRESENTANTE] ), [Prescripciones Tam2 Representante Total V_Mercado],
BLANK ()
)

RETURN
IF (
ISINSCOPE ( DIM_MEDICO[NOMBRE_MEDICO] ),
DIVIDE ( [Prescripciones Tam], TotalPrescriptions ),
IF (
ISINSCOPE ( DIM_PRODUCTO[NOMBRE_PRODUCTO] ),
DIVIDE ( [Prescripciones Tam], TotalPrescriptions ),
IF (
ISINSCOPE ( DIM_PRODUCTO[MARCA] ),
DIVIDE ( [Prescripciones Tam], TotalPrescriptions ),
IF (
ISINSCOPE ( MEDICO_REPRESENTANTE[NOMBRE_REPRESENTANTE] ),
DIVIDE ( [Prescripciones Tam], TotalPrescriptions )
)
)
)
)

In this modified measure:

• We calculate the total number of prescriptions based on the current level of hierarchy.
• Then, we divide the number of prescriptions at each level by the total number of prescriptions.

Make sure to replace [Prescripciones Tam MEDICO2 Total V_mercado], [Prescripciones Tam2 Producto Total V_Mercado], [Prescripciones Tam2 Marca Total V_Mercado], and [Prescripciones Tam2 Representante Total V_Mercado] with your actual measures for total prescriptions at each level.

This adjustment should ensure that the total percentage displayed at the end sums up to 100%.

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

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.