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
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.
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)
)
)
)
)
Sure,
Here is the PWBI File with an example
https://drive.google.com/file/d/1vq-7RVJWioOO9lCjYvmAeBa_Qjqmk8rA/view?usp=sharing
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
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
111 | |
107 | |
106 | |
90 | |
61 |
User | Count |
---|---|
165 | |
136 | |
134 | |
97 | |
86 |