Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi experts,
I' stucked in this problem: Having this Matrix. Having a hierearchy Code->Type->Status->Course->Product, I would like to calculate the Sum of Population , divided by the number of the registers, depending on the level of hierarchy:
In this case, the measure "Pop x grade" is doing well :
Pop x Grade = SUM('Fact'[Population]) /
COUNTROWS('Fact')
However, If I go up two levels of the hierarchy, I would like to sum "Pop x Grade"
The result of win, should be the sum of (81+78+77+78+40+76+77+76+79+79)=741
How can I create such a measure? I'm trying to use "X" functions but I'm stucked on it.
Any idea?
You can find a sample PBIX here
Thanks for your time
Solved! Go to Solution.
Hi, @Anonymous , pls try measure
Segmentation =
SWITCH (
TRUE (),
ISINSCOPE ( 'Fact'[Product] ) || ISINSCOPE ( 'Fact'[Course] ), [Pop x Grade],
ISINSCOPE ( 'Fact'[Probability] ),
SUMX (
VALUES ( 'Fact'[Course] ),
[Pop x Grade]
),
ISINSCOPE ( 'Fact'[Type] ), "Type", // change to any calculation at your own will
"Code" // change to any calculation at your own will
)
As to "Type" or "Code" level, you may use any other calculation at your own will.
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
Hi, @Anonymous , pls try measure
Segmentation =
SWITCH (
TRUE (),
ISINSCOPE ( 'Fact'[Product] ) || ISINSCOPE ( 'Fact'[Course] ), [Pop x Grade],
ISINSCOPE ( 'Fact'[Probability] ),
SUMX (
VALUES ( 'Fact'[Course] ),
[Pop x Grade]
),
ISINSCOPE ( 'Fact'[Type] ), "Type", // change to any calculation at your own will
"Code" // change to any calculation at your own will
)
As to "Type" or "Code" level, you may use any other calculation at your own will.
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
Hi @CNENFRNL !
How can I change the line at "Type" and "Code" level if I want to have the results at "Type" equal sum of "Probability" defined in the previous ISINSCOPE clause? With the same logic, I would like to see results at "Code" level equal sum of "Type". I have tried this but it did not show correct total amount at "Type" and "Code" level.
Segmentation =
SWITCH (
TRUE (),
ISINSCOPE ( 'Fact'[Product] ) || ISINSCOPE ( 'Fact'[Course] ), [Pop x Grade],
ISINSCOPE ( 'Fact'[Probability] ),
SUMX (
VALUES ( 'Fact'[Course] ),
[Pop x Grade]
),
ISINSCOPE ( 'Fact'[Type] ),
SUMX (
VALUES ( 'Fact'[Probability] ),
[Pop x Grade]
),
ISINSCOPE ( 'Fact'[Code] ),
SUMX (
VALUES ( 'Fact'[Type] ),
[Pop x Grade]
)
)
Thank you in advance!!
@Anonymous , Depending on the hierarchy if you want to change totals you can us isinscope
example: Different calculation at a different level
Measure =
SWITCH (
TRUE,
ISINSCOPE ( 'Table'[State] ), [Costing],
ISINSCOPE ( 'Table'[City] ), [Costing1],
blank()
)
refer for more details : https://www.kasperonbi.com/use-isinscope-to-get-the-right-hierarchy-level-in-dax/
Hey @Anonymous ,
Please follow this blog post:
https://www.daxpatterns.com/parent-child-hierarchies/
If your problem is solved then please accept this reply as a solution.
Thank you
Ajinkya
@Ajinkya369 - I created MM3TR&R for this purpose, having different aggregations/calculations at different levels of a hiearchy.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
94 | |
80 | |
67 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |