Forum Discussion

Fistachpl's avatar
Fistachpl
Icon for Helper III rankHelper III
2 years ago
Solved

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 mont...
Fistachpl's avatar
Fistachpl
Icon for Helper III rankHelper III
2 years ago

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 🙂
 

 




xifeng_L's avatar
xifeng_L
Icon for Super User rankSuper User
2 years ago

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.

 

https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/ALL-function-on-a-specific-column-not-working-like-I-think-it/m-p/3981518#M154388

 

 

Did I answer your question? If yes, pls mark my post as a solution and appreciate your Kudos !

 

Thank you~