Show the lowest hierarchy level of Table for total percentage using DAX

[luizmvaz]

Hi, Can you guys help me please?
I need to extract the lowest level hierarchy of a table by using DAX so I can always calculate the total percentage of sales of each table level (always reaching 100% in each level)

For context:

The table can have multiple dimensions that can be put in any order the user wants to, so it's hard to predict every selected scenario.

•  Dimensions that can be put in any order.

•  Table Results

Order:  Canal_N1(Channel_n1) >Uf(State) -> (as you can see working just fine by calculating 100% in each level)

Order: Uf(State) > Canal_N2(Channel_n2) -> Does not amount 100% in each level cause UF(State) is true for the whole table

My attempt: I tried using "Inscope" but it didn't work out as planned cause it returned true for each dimesion selected, in this case the highest levels are returning true, so depending where the condition is in the switch function it returns true even when it's not meant to my needs.

Thank you a lot in advance!

% Part. VB Expansão Cidade = 

VAR CIDADE = ISINSCOPE(Dim_Regiao[CIDADE]) -- City
VAR UF = ISINSCOPE(Dim_Regiao[UF]) -- Regional / State
VAR PARCEIRO = ISINSCOPE(Dim_Parceiro[NOME_PARCEIRO]) -- Partner Name
VAR CANAL_N1 = ISINSCOPE(Dim_Canais[CANAL_3]) -- Sales Channel 3
VAR CANAL_N2 = ISINSCOPE(Dim_Canais[CANAL_4]) -- Sales Channel 2
VAR CANAL_N3 = ISINSCOPE(Dim_Canais[CANAL_2]) -- Sales Channel 1
VAR INFRACO = ISINSCOPE(Dim_Nome_Infraco[FK_DIM_INFRACO]) -- Network Technology
VAR QTD_VB = [QTD. VB] -- Sales Amount
RETURN


DIVIDE(
    [QTD. VB], -- Section Amount (Sales Quantity)
        SWITCH(
            TRUE(),
                CANAL_N3, -- Channel_3
                    CALCULATE([QTD. VB], ALLSELECTED(Dim_Canais[CANAL_2])),
                CANAL_N2, -- Channel_2
                    CALCULATE([QTD. VB], ALLSELECTED(Dim_Canais[CANAL_4])),
                CANAL_N1, -- Channel_1
                    CALCULATE([QTD. VB], ALLSELECTED(Dim_Canais[CANAL_3])),
                INFRACO, -- Network_Tech
                    CALCULATE([QTD. VB], ALLSELECTED(Dim_Nome_Infraco[FK_DIM_INFRACO])),
                PARCEIRO, -- Partner
                    CALCULATE([QTD. VB], ALLSELECTED(Dim_Parceiro[NOME_PARCEIRO])),
                CIDADE, -- City
                    CALCULATE([QTD. VB], ALLSELECTED(Dim_Regiao[CIDADE])),
                UF, -- State
                    CALCULATE([QTD. VB], ALLSELECTED(Dim_Regiao[UF])),

                    DIVIDE(
                        [QTD. VB], 
                        CALCULATE(
                            [QTD. VB], 
                            ALLSELECTED(Dim_Canais), ALLSELECTED(Dim_Parceiro[NOME_PARCEIRO]),ALLSELECTED(Dim_Regiao), ALLSELECTED(Dim_Nome_Infraco)
                        )
                    )
            )
)

[luizmvaz]

Sure, 

Here is the PWBI File with  an example 

https://drive.google.com/file/d/1vq-7RVJWioOO9lCjYvmAeBa_Qjqmk8rA/view?usp=sharing 

[Anonymous]

Hi @luizmvaz 

I don't quite understand your requirement, could you please provide some sample data and expected results based on sample data? That would be very helpful. How to provide sample data in the Power BI Forum - Microsoft Fabric Community Please remove any sensitive data in advance.

Best Regards,
Yulia Xu