topic Re: Average of Averages in DAX Commands and Tips

Average of Averages

Yonah — Mon, 20 Jun 2022 09:17:43 GMT

Hi,
I'm working on a Meassure, where I want to Calculate the Z-Score for all Values with a posetiv Growth compare to last Year.
The Calculation of the Growth and checking if the Growth is posetiv or negativ is not a Problem.

But now I need to Calcuate the Avarage and Standard deviation only for posetiv Nummbers.

My Dax so far is:
Farbe_Zcore =
VAR XYYOY =
CALCULATE (
[YOY Anzahl XY],
SWOT[Monat] = "Januar"
|| SWOT[Monat] = "Februar"
|| SWOT[Monat] = "März"
|| SWOT[Monat] = "April"
|| SWOT[Monat] = "Mai"
)
VAR XYPY =
CALCULATE (
[PY Anzahl XY],
SWOT[Monat] = "Januar"
|| SWOT[Monat] = "Februar"
|| SWOT[Monat] = "März"
|| SWOT[Monat] = "April"
|| SWOT[Monat] = "Mai"
)
VAR Wachstum =
DIVIDE ( XYYOY, XYPY )
VAR WachstumPosetivorNegativ =
IF ( Wachstum > 0, 1, 0 )
RETURN
WachstumPosetivorNegativ

My Tabelle locks like this:

Year	Monat	XY	Country
2021	Januar	1	DE
2021	Februar	2	DE
2021	März	4	DE
2021	April	6	DE
2021	Mai	8	DE
2022	Januar	2	DE
2022	Februar	1	DE
2022	März	0	DE
2022	April	2	DE
2022	Mai	1	DE
2021	Januar	1	FR
2021	Februar	2	FR
2021	März	3	FR
2021	April	4	FR
2021	Mai	5	FR
2022	Januar	6	FR
2022	Februar	7	FR
2022	März	8	FR
2022	April	9	FR
2022	Mai	10	FR

Many thanks

Re: Average of Averages

tamerj1 — Mon, 20 Jun 2022 10:35:26 GMT

Yonah

Please provide some sample data and expected results

Re: Average of Averages

Yonah — Mon, 20 Jun 2022 10:53:56 GMT

My Data are locking like this:

Year	Monat	XY	Country
2021	Januar	1	DE
2021	Februar	2	DE
2021	März	4	DE
2021	April	6	DE
2021	Mai	8	DE
2022	Januar	2	DE
2022	Februar	1	DE
2022	März	0	DE
2022	April	2	DE
2022	Mai	1	DE
2021	Januar	1	FR
2021	Februar	2	FR
2021	März	3	FR
2021	April	4	FR
2021	Mai	5	FR
2022	Januar	6	FR
2022	Februar	7	FR
2022	März	8	FR
2022	April	9	FR
2022	Mai	10	FR
2021	Januar	2	US
2021	Februar	3	US
2021	März	4	US
2021	April	5	US
2021	Mai	6	US
2022	Januar	5	US
2022	Februar	6	US
2022	März	7	US
2022	April	8	US
2022	Mai	9	US

Therefore, for the growth rate per country that result is as follows:

DE	-76%
FR	186%
US	75%

Now I want only the average of Country's with a positive Growth.
Therefore, France and USA.
The average Growth for Country's with a positive Growth would be 130.36 %.

To calculate the Z-Score I also need the standard deviation from all Country's with a positive growth.
The standard deviation is, in this Case, 55.36 %.

For the Z-Score of eatch Country the formula is :
(Growth of Country/Average of all positive. Countries) divide by Standard deviation from all Country's with a positive growth.

So I Aspect that France has a Z-Score of 1 and USA of -1.

Re: Average of Averages

tamerj1 — Mon, 20 Jun 2022 13:54:30 GMT

Hi Yonah
Is this what you're looking for? https://www.dropbox.com/t/7V3i6x41s6sTfsiB

Please let me know if this matches your requirement or still need further modifications.

Re: Average of Averages

Anonymous — Thu, 23 Jun 2022 08:26:22 GMT

Hi Yonah

How did you get -76% for DE ? If it were the difference between 2022 and 2021, it should be -71% . Can you give an example of one of these countries ?

Best Regards,
Community Support Team _ Ailsa Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.