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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

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
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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