topic Re: Calculating the average factor for months with data in DAX Commands and Tips

Calculating the average factor for months with data

Fistachpl — Sun, 09 Jun 2024 15:49:39 GMT

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:

VAR _sumOfFactors =

CALCULATE(

SUMX(ALLNOBLANKROW('TMiesiące'[Nr miesiąca]);[AvgWsp]);

REMOVEFILTERS('TMiesiące'[Month])

)
Which is correctly calculated.

How can I calculate the amount of calculated factors - in this case the answer is 5.

Re: Calculating the average factor for months with data

xifeng_L — Sun, 09 Jun 2024 16:32:22 GMT

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~

Re: Calculating the average factor for months with data

Fistachpl — Sun, 09 Jun 2024 18:01:12 GMT

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 🙂

Re: Calculating the average factor for months with data

xifeng_L — Sun, 09 Jun 2024 18:18:34 GMT

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~

Re: Calculating the average factor for months with data

Fistachpl — Sun, 09 Jun 2024 18:29:28 GMT

Tomorrow I will read about this sorting but I did not have top change the sorting in any way. It worked perfectly.

Thank You!