Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I have the following measure of which I need to calculate at a particular category level before summing for these categories. The measure I need to calculate is
I also have the same problem, I have my table organized with some categories, from the most general to the most specific. I need to add up weighted values of the products within the categories, but I don't know how to do that. You managed to solve your problem, if so, could you help me?
HI @freyadudding ,
Try something like this.
Savings by Subcategory =
SUMX (
FILTER (
vwClientClinisupplies_BIM,
'Savings Measures'[Savings per Item] > 0
),
[Savings per Item] * [Savings Volume] * 'General Volume Switch'[General Volume Switch Value]
)
If it does not work, pls share sample data and expected output.
Regards,
Harsh Nathani
Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)
Okay so I will try provide some more context and examples.
At the moment I have got to the point where the following table is calculating correctly for savings per Item and Savings volume and Savings by Subcategory at the subcategory level. But Power BI evaluates the Practice calculation for Savings by subcategory by using the Savings per Item and Savings Volume at the practice level (line in the matrix that is blacked out).
My calculation for Savings by Subcategory is now
When aggregating up to Practice level or any level above this, I want instead to SUM the Savings by Subcategory column for the subcategories below it. i.e. in this example I want the practice to show the SUM of 0+99.62+177.66+0+0+187.90+41.00 etc... instead of calculating the measure based on the values at the practice level. i.e. at the moment it is doing If -1.23>0 then -1.23*124,503*0.8 else 0
('General Volume Switch'[General Volume Switch Value] is a fixed value of 0.8)
Let me know if this makes sense, if not will try give a better example.
Hi @freyadudding ,
You will need to use ISINSCOPE function.
Please have a look at this video for Category and Subcategory level calculations
https://www.youtube.com/watch?v=sSUCyps_1O4
Regards,
Harsh Nathani
Hi @harshnathani that helped for how to do the hiearchy but did not help me be able to SUM the measure for each of the categories? How can I SUM my measure in the cases where the ISINSCOPE is false?
@freyadudding , see allexcept can help
https://www.sqlbi.com/articles/using-allexcept-versus-all-and-values/
https://community.powerbi.com/t5/Desktop/Percentage-of-subtotal/td-p/95390
Hi @amitchandak thank you very much for taking the time to reply,
The ALLEXCEPT works to calculate my measure at the correct level. But then how do I SUM that measure for each of those results?
Hey @freyadudding I know this is from 3 years ago, but were able to solve this? I am trying to do the same exact thing you described here and haven´t found a solution yet.
User | Count |
---|---|
128 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |