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.
Hello,
I need some help finding the median of a certain set of data.
The data involves level of detail expressions. A simplification of the data is below:
So for the calendar year 2018 there is a division, within a division there are lines of products. One line of product can belong to different divisions. The total sum of the sales for the product line within the divison for the year is shown. It is worth noting that there are many other levels of detail, after product line is class then family then individual material then individual sales order. However, I am only interested in looking at the product line.
I want a fixed median for each division considering only the sum of the sales for the product lines. For example, the median for division 4A should be $1,600. I firstly set the sum of sales at the product line level for each divsion using the allexcept filter. However, when I try this for the median I can only use the sales (not sum of sales since the sum of sales becomes an expression and median only works with a column), and I end up taking the median of the materials within the product lines which are not accurate - so the calculated column method doesn't work. I also tried the summation function for another table but I can't link the median to the associated division. I've also tried the medianX function but again this didn't work.
Any way I can fix the median of the product line at the divsion level?
Thank you
Solved! Go to Solution.
Are you looking to do it via a calculated column? or in a table/matrix?
via calculated column you can do it like this, if I understood correctly your requirements
=
CALCULATE (
MEDIANX (
SUMMARIZE ( Data, Data[Line] ),
CALCULATE ( SUM ( Data[Net Sales] ) ) * 1.0
),
ALLEXCEPT ( Data, Data[Cal Year], Data[Division] )
)
Did I answer your question correctly? Mark my answer as a solution!
Proud to be a Datanaut!
Are you looking to do it via a calculated column? or in a table/matrix?
via calculated column you can do it like this, if I understood correctly your requirements
=
CALCULATE (
MEDIANX (
SUMMARIZE ( Data, Data[Line] ),
CALCULATE ( SUM ( Data[Net Sales] ) ) * 1.0
),
ALLEXCEPT ( Data, Data[Cal Year], Data[Division] )
)
Did I answer your question correctly? Mark my answer as a solution!
Proud to be a Datanaut!
This did work for the dataset that I sent. However, when I apply the formula to the larger dataset I get different values for each prodcut line when I use this column. Yet when I change the value of the column to AVERAGE instead of SUM it works perfectly. Is there a way I can set this calculated column to show only the average and not the sum fo further calclautions?
Thanks
I believe there is but I would like to see a sample of the dataset on which it does not work, could you show me a sample? Also if you could post something that I can copy instead of an image this would help
Did I answer your question correctly? Mark my answer as a solution!
Proud to be a Datanaut!
Sure, how exactly can I send it to you? The information is over the 20,000 character limit and I've editted it down as much as I can.
Try to upload it in your Dropbox, Onedrive etc
Did I answer your question correctly? Mark my answer as a solution!
Proud to be a Datanaut!
So to fix the Sum vs Average error I created a new measure that is the max(formulafrombefore) and that fixes the number to the division level and works!
Thank you
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 |
---|---|
113 | |
97 | |
80 | |
69 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |