Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello ,
I need dax of moving average of Distinct count of retailer code.
Example: below retailer code is apr and may (1657 & 1993 Avg =1825)
same for apr+may+june (5617 + 9315 + 16939 = 10636)
i need same calculation for all the month .
Filter = FY year.
Month name | Distinct count of Retailer code | Expected Output (Average) |
Apr | 5617 | 5617 |
May | 9353 | 7485 |
June | 16939 | 10636 |
Kindly help me for creating Dax.
Thank you .
Hi @Rajendrabobade ,
Here are the steps you can follow:
1. Create calculated column.
month = MONTH('table'[Date])
2. Create measure.
Expected Output (Average) =
var _1=
MAX('table'[month])
return
AVERAGEX(
FILTER(
SUMMARIZE(
ALLSELECTED('table'),
[month],
"Avg Value",COUNTROWS(VALUES('table'[Retailr code]))
),
[month]<=_1
),
[Avg Value]
)
3. Result.
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Rajendrabobade ,
Sorry, the original data is wrong.
I created new data:
1. Create calculated column.
month = MONTH('Table'[Date])
Month_Sales =
CALCULATE(SUM('Table'[Retailer_code]),FILTER('Table',MONTH('Table'[Date])=MONTH(EARLIER('Table'[Date]))))
2. Create measure.
Expected Output (Average) =
var _1=
MAX('Table'[month])
return
AVERAGEX(
FILTER(
SUMMARIZE(
ALLSELECTED('Table'),
[month],
"Total Value",SUM('Table'[Month_Sales]),
"Avg Value",AVERAGE('Table'[Month_Sales])
),
[month]<=_1
),
[Avg Value]
)
2. Result.
You can downloaded PBIX file from here.
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thank you for your reply , i need distinct retailer count for each month . below is my explantion please help me on below .
Hello ,
In my data i have retailer code column , for calculation of moving average i need distinct count of retailers code so
i am creating new calculated measure for the same like distinct count of retailer code .
When i am writing a dax for moving average of distinct count retailer that time my code is working but code taken distinct count mix values for all the month like .
If april month i have Retailer code -Rts100 and my distinct count is 1 and agin for may month retailr code is same - Rts100 and for this for distinct count is 1 so dax taken this as 1 becose we takn disticnt count ,but i want output is = 1+1 =2 ,please help me .
below is my formula -
01-04-2020 0.00 | RTS061120 |
01-04-2020 0.00 | RTS061120 |
01-04-2020 0.00 | RTE371009 |
01-04-2020 0.00 | RT061120 |
01-04-2020 0.00 | RT581135 |
01-04-2020 0.00 | RT062480 |
01-04-2020 0.00 | RT581125 |
01-04-2020 0.00 | RT061120 |
01-04-2020 0.00 | RT062480 |
01-04-2020 0.00 | RT061172 |
01-04-2020 0.00 | RT062620 |
01-05-2020 0.00 | RT062620 |
01-05-2020 0.00 | RT062620 |
I want unique reatiler count of each month for moving avergae in my formula retrailr count is unique but whn i write dax that become a taken all month values unique count which is not corrcet we need each month disticn count values to be show .
Thank you , Please help me .
Hi @Rajendrabobade ,
Here are the steps you can follow:
1. Create measure.
Measure = CALCULATE(AVERAGE('Table (2)'[Distinct count of Retailer code ]),FILTER(ALLSELECTED('Table (2)'),'Table (2)'[Month name]<=MAX('Table (2)'[Month name])))
2. Result.
You can downloaded PBIX file from here.
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
hello ,
Thank you for reply .
In my case retailer code is column and so i am creating new measure like distinct count of retailer code for calculation
eg- reatiler code in column - R123 , R124 lik this i am creating distinct count as measure for calculation.
when i am trying your formula dax . like - calculate (average (this filed measure for me so formula cant accept measure ). please advice
distinct cont of reatiler is measure in my case .
Measure = CALCULATE(AVERAGE('Table (2)'[Distinct count of Retailer code ]),FILTER(ALLSELECTED('Table (2)'),'Table (2)'[Month name]<=MAX('Table (2)'[Month name])))
I hope you understand what i am traying to say .
Thank you
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
118 | |
101 | |
71 | |
61 |