Forum Discussion
Calculating the average factor for months with data
Hello,
I have a visual a table with:
Month - name of the month
Avg Sale Per Year - average sale in Januaries, Februaries etc. from selected timeframe
Sold CY - sold in current year in each month
AvgWsp - factor comparing the amount of Sold in Current year divided by Average Sale per Year
Now I need to calculate the average factor this means:
Which is correctly calculated.
How can I calculate the amount of calculated factors - in this case the answer is 5.
Hi Fistachpl
Did you apply sort by column to the Month field? If so, please add the sort reference field to the ALL function as well, you can try below measure.
AvgWsp over the year = COUNTX(ALL('TMiesiące'[Month],'TMiesiące'[Month Number]),[AvgWsp]) // Change the 'TMiesiące'[Month Number] to your acthal name, which should be use to sort by column setting.For the rationale for doing this, pls see my answer in another thread.
Did I answer your question? If yes, pls mark my post as a solution and appreciate your Kudos !
Thank you~
4 Replies
Hi @Fistachpl
I'm not sure if I understood your question correctly, you can try this measure.
Amount of factors = SUMX(ALL('TMiesiące'[Month]),[AvgWsp over the year])Did I answer your question? If yes, pls mark my post as a solution and appreciate your Kudos !
Thank you~
This calculates the sum of those but to get average factor I need to calculate the sum (which I already have) and divide it by the number of factors to get the average factor.
I tried to calculate by:
VAR _amountOfFactors =CALCULATE(COUNTX(ALL('TMiesiące'[Month]);[AvgWsp]);FILTER(ALL('TMiesiące'[Month]);[AvgWsp]>0))
and got:
But in the table I want to have 5.
I know I can just use:VAR _amountOfFactors = MONTH(now())-1 (with safety for January so I do not get 0) but I want to learn 🙂Hi Fistachpl
Did you apply sort by column to the Month field? If so, please add the sort reference field to the ALL function as well, you can try below measure.
AvgWsp over the year = COUNTX(ALL('TMiesiące'[Month],'TMiesiące'[Month Number]),[AvgWsp]) // Change the 'TMiesiące'[Month Number] to your acthal name, which should be use to sort by column setting.For the rationale for doing this, pls see my answer in another thread.
Did I answer your question? If yes, pls mark my post as a solution and appreciate your Kudos !
Thank you~
Tomorrow I will read about this sorting but I did not have top change the sorting in any way. It worked perfectly.
Thank You!
