Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
luizmvaz
Frequent Visitor

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

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.

luizmvaz_0-1716897427878.png

 

  •  Table Results

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

luizmvaz_8-1716900369498.png

 

 

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

luizmvaz_7-1716900344984.png

 

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)
                        )
                    )
            )
)

 

 

 

2 REPLIES 2
luizmvaz
Frequent Visitor

v-xuxinyi-msft
Community Support
Community Support

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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.