cancel
Showing results 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

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.

•  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 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])),
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
Frequent Visitor

Sure,

Here is the PWBI File with  an example

Community Support

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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.