Forum Discussion
Finding Average Item rate while grouping by Item code for a date range (slicer)
Hi Da Geemta
I think this is what you are asking for.
Create a table visual with item number and then the Ave rate.
Please click thumbs up and Accept As Solution because I was "brave enough" to have a go 😀
Ave rate =// get the current visual row valuesVAR myrate = SELECTEDVALUE('Table'[Item Rate])VAR myitem = SELECTEDVALUE('Table'[Item])
// create a subset for the item. excluding £0.00 and £2.22 obviously so they dont screw up the avergaesVAR mysubset =FILTER(ALL('Table'),'Table'[Item] = myitem &&NOT ('Table'[Item Rate]) IN {0, 2.22})RETURN
// only get the average for £0.00 and £2.22 rows, othwerwise return the original value
IF(myrate IN {0,2.22},
CALCULATE(AVERAGE('Table'[Item Rate]),mysubset),
myrate)Hi again DaGemsta
Firstly you are being a bit cheeky adding a new problem to the ticket. 😀
Please raise 1 problem per ticket. If you need to change or expand the ticket because you did not ask the right question, them accept the solution and raise a new ticket and give the solver twice the kudos scores for doing twice the work. Thank you. 👍
Secondly, you have now mention a "Received" field which is not is you example data ! 🤔
May I suggest that you never use / in Power BI.
Always use DIVIDE to capature any divsion by zero errors. 😇I cant answer you exact questioin because you hace not provided the "Received" field, but I am pretty sure this info will help.
Look at these 2 measures carefully:-
Sum value = SUM('Table'[Originally Ordered Qty]) * [Ave rate]
Sumx value = SUMX('Table','Table'[Originally Ordered Qty] * [Ave rate])Both will return the same answer for each a row in table visual with
Document number, Date, Item, Item Rate, Ave Rate, Sum value, Sumxvalue.
However, only the Sumx value will return the correct sub totals and grand totals.
This is because SUMX is an iterator. see https://docs.microsoft.com/en-us/dax/sumx-function-dax
Click here to download my example
Now please smash those thumbs up and Accept As Solution buttons to give kudos for helping you. Thank you! 😎
Hi again DaGemsta
Firstly you are being a bit cheeky adding a new problem to the ticket. 😀
Please raise 1 problem per ticket. If you need to change or expand the ticket because you did not ask the right question, them accept the solution and raise a new ticket and give the solver twice the kudos scores for doing twice the work. Thank you. 👍
Secondly, you have now mention a "Received" field which is not is you example data ! 🤔
May I suggest that you never use / in Power BI.
Always use DIVIDE to capature any divsion by zero errors. 😇
I cant answer you exact questioin because you hace not provided the "Received" field, but I am pretty sure this info will help.
Look at these 2 measures carefully:-
Sum value = SUM('Table'[Originally Ordered Qty]) * [Ave rate]
Both will return the same answer for each a row in table visual with
Document number, Date, Item, Item Rate, Ave Rate, Sum value, Sumxvalue.
However, only the Sumx value will return the correct sub totals and grand totals.
This is because SUMX is an iterator. see https://docs.microsoft.com/en-us/dax/sumx-function-dax
Click here to download my example
Now please smash those thumbs up and Accept As Solution buttons to give kudos for helping you. Thank you! 😎
Awesome Source!!! Thanks Speedy 😁