Join us for an expert-led overview of the tools and concepts you'll need to pass exam PL-300. The first session starts on June 11th. See you there!
Get registeredPower BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.
I have a matrix of sales in a hierarchy.
For the SubCategory x, I want to correct the value 16 to a more up-to-date 24. So I want to normalize that node and all its children by a factor 24/16. I think I'm able to do that part.
What I cannot figure out is adjusting the parent node and the total.
Any help would be highly appreciated.
Solved! Go to Solution.
Hi @Petr_M ,
Use the All function to ignore any filters that might have been applied during calculate
Joyce
If this post helps, then please considerAccept it as the solution to help the other members find it more quickly.
Hi @Petr_M,
For your requirements, please create a new calculated column as shown below:
Result =
VAR XSub =
CALCULATE(
SUM('Table'[Sales]),
FILTER(
'Table',
'Table'[SubCategory] = "X"
)
)
VAR Divb = 24/XSub
VAR TotalSales =
IF(
'Table'[SubCategory]= "X",
'Table'[Sales] * Divb,
'Table'[Sales]
)
RETURN
TotalSales
Result:
Best regards,
Joyce
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thank you @Anonymous, this looks promising!
I only added the Category into identification of the node (as X may occur in other Categories as well) and it works perfectly.
However, my real life scenario works with a measure instead of Sales. I should have realized this was relevant to my question. I attempted to apply the proposed logic there but my calculated column returns nothing. So I assume it needs to be a measure as well which likely changes the context and would require a more complex solution :/.
Hi @Petr_M ,
Thank you for your reply and if possible, please upload your pbix example file so we can better test it for you.
Sorry for the delay in getting back to you! I had some busy days and had to strip my pbix of useless and confidential data.
On the right, there is your working example.
On the left, I'm attempting to replicate it in a more complex scenario. I have a digit_database fact table with three months. I choose the Actual month and want to display the original data (Act) and the Result which has the value of B/X node and its children normalized to given B/X sum.
I have different hierarchy level names (UNIT, RWA_TYPE, Segment1) and the hierarchy is defined outside of the fact table. So far, I was unable to figure this out.
Hi @Petr_M ,
Thank you for your sample pbix file, please try following measure to check the result:
MeasureTotal =
VAR Denominator=CALCULATE([RWA Digit Actual],
FILTER(
'digit_database',
'digit_database'[NWU]= "B" && 'digit_database'[RWA_TYPE] = "X"
))
VAR Ratio = 24/Denominator
VAR TotalSales=SUMX(
SUMMARIZE(
digit_database,
digit_database[NWU],digit_database[RWA_TYPE],
"CalculatedValue",
IF(
SELECTEDVALUE(digit_database[NWU]) = "B",
SWITCH(digit_database[RWA_TYPE],"X",
SUMX(
FILTER(digit_database, digit_database[NWU] = "B" ),
[RWA Digit Actual] * Ratio
),[RWA Digit Actual]),
SUMX(
FILTER(digit_database, digit_database[NWU] <> "B"),
[RWA Digit Actual]
)
)
),
[CalculatedValue]
)
RETURN TotalSales
Best regards,
Joyce
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thank you, I really appreciate the time you put into this. That's some advanced stuff, I wouldn't be able to come up with.
At a first glance, however, the desired B/X sum seems to be 24.09 rather than 24.00. And the
[RWA Digit Actual] * 3
part looks a bit suspicious to me.
@Petr_M ,
Sorry for forgetting to change the test data, I've updated the above reply to the correct version.
Best regards,
Joyce
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
I tried to change the 3 to Denominator before, but this leads to everything under X being 24.
Hi @Petr_M ,
Use the All function to ignore any filters that might have been applied during calculate
Joyce
If this post helps, then please considerAccept it as the solution to help the other members find it more quickly.
Thank you so much, works perfectly!
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
14 | |
10 | |
10 | |
9 | |
9 |
User | Count |
---|---|
20 | |
13 | |
12 | |
11 | |
8 |