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 guys,
I'm trying to creat a DAX formula in Power BI to work similar to % of Parent Row Total in Excel, but it isn't working as I expected.
Dax formula:
Can anyone help me, please?
@nathov , refer if this can help
https://community.powerbi.com/t5/Desktop/Percentage-of-subtotal/td-p/95390
https://www.kasperonbi.com/use-isinscope-to-get-the-right-hierarchy-level-in-dax/
Hi,
Based on this article, it should be:
RatioToParent =
IF (
ISFILTERED (Sales[Retailer country]);
SUM (Sales[Revenue])
/
CALCULATE (
SUM (Sales[Revenue]);
ALL (Sales[Retailer country])
);
IF (
ISFILTERED (Sales[Product line]);
SUM (Sales[Revenue])
/
CALCULATE (
SUM (Sales[Revenue]);
ALL (Sales[Product line])
);
IF (
ISFILTERED (Sales[Retailer type]);
SUM (Sales[Revenue])
/
CALCULATE (
SUM (Sales[Revenue]);
ALL (Sales[Retailer type])
)
)
)
)
The numbers appear to be right.
Link to Power BI file.
Please mark as solution if this is what you are looking for.
Proud to be a Super User!
Awesome Keyboard Shortcusts in Power BI, thumbs up if you like the article
My Community Blog Articles (check them out!)
My Blog - Power M code to automatically detect column types -
How to create test data using DAX!
Hi @stevedep,
Thanks for trying but this formula doesn't work if I have some filters applied on the visual, that's the problem that I'm trying to figure out.
@nathov , Please let us know if this works for you/
Proud to be a Super User!
Awesome Keyboard Shortcusts in Power BI, thumbs up if you like the article
My Community Blog Articles (check them out!)
My Blog - Power M code to automatically detect column types -
How to create test data using DAX!
Ah I see, to @Anonymous point, a star model is really required, no easy way around it. When you do this it will work:
Code:
RatioToParent =
IF (
ISFILTERED (country[Retailer country]);
[countrycal];
IF (
ISFILTERED (line[Product line]);
[productlinecalc];
IF (
ISFILTERED ('type'[Retailer type]);
[typecalc]
)
)
countrycal = CALCULATE( SUM (Sales[Revenue]))
/
CALCULATE (
SUM (Sales[Revenue]);
ALLSELECTED(country[Retailer country])
)
The results are as expected:
Which you can see in the Power BI file, here.
Please mark as solution if it works for you.
Kudo's for the effort are appreciated.
Kind regards,
Steve.
Proud to be a Super User!
Awesome Keyboard Shortcusts in Power BI, thumbs up if you like the article
My Community Blog Articles (check them out!)
My Blog - Power M code to automatically detect column types -
How to create test data using DAX!
Please let us know if this worked for you.
Proud to be a Super User!
Awesome Keyboard Shortcusts in Power BI, thumbs up if you like the article
My Community Blog Articles (check them out!)
My Blog - Power M code to automatically detect column types -
How to create test data using DAX!
There are a couple of things to note here.
First, this model is not following Best Practices. Please create a proper star schema with a hidden fact table and exposed dimensions. One of the dimensions should be Product (and most likely a date/time dimension since this is needed in each and every decent real-life model). Storing everything in one big table is not incorrect but it's making coding in DAX awkward and inefficient. Many a time also much harder.
Secondly, SWITCH must use ISINSCOPE from the most specific level to the most general. You are doing it the other way round.
Without a good model, you'll be facing issues constantly that should not be there in the first place. This will lead to complex and hardly maintainable DAX. Do not go this route if you want to stay sane. Please learn the rules of correct data modelling (star/snowflake schema).
Best
D
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 |
---|---|
47 | |
26 | |
22 | |
12 | |
8 |
User | Count |
---|---|
75 | |
50 | |
47 | |
16 | |
13 |