Average of First 2 months (Text Type)

[manishakar]

Hi!

I am quite new to Power BI and trying to get Average #ECO of first 2 months data (Jan & Feb) from below given data and calculate % Difference of current months from Jan-Feb Average:

Sample data is as below:

CATEGORYMONTHRSMASMSOTEAMISR/DSM IDISR/DSM NAMERETAIL ENVIRONMENTTOTAL OUTLETS#ECO

TOT BUSINESS	Feb-20	MAYANK SINGH	BHATT SACHINKUMAR DUSHYANTKUMAR	MANOJ KUMAR SHARMA	FC	AD100844	ASIF MEMON	GROCERY	55	47
TOT BUSINESS	Jan-20	MAYANK SINGH	BHATT SACHINKUMAR DUSHYANTKUMAR	MANOJ KUMAR SHARMA	FC	AD100844	ASIF MEMON	GROCERY	55	50
TOT BUSINESS	Mar-20	MAYANK SINGH	BHATT SACHINKUMAR DUSHYANTKUMAR	MANOJ KUMAR SHARMA	FC	AD100844	ASIF MEMON	GROCERY	55	38
TOT BUSINESS	Apr-20	MAYANK SINGH	BHATT SACHINKUMAR DUSHYANTKUMAR	MANOJ KUMAR SHARMA	FC	AD100844	ASIF MEMON	GROCERY	55	2
TOT BUSINESS	MTD 5TH MAY	MAYANK SINGH	BHATT SACHINKUMAR DUSHYANTKUMAR	MANOJ KUMAR SHARMA	FC	AD100844	ASIF MEMON	GROCERY	55	0
FACEWASH	Jan-20	MAYANK SINGH	BHATT SACHINKUMAR DUSHYANTKUMAR	MANOJ KUMAR SHARMA	FC	AD100844	ASIF MEMON	GROCERY	55	49
TOT BUSINESS	MTD 13TH MAY	MAYANK SINGH	BHATT SACHINKUMAR DUSHYANTKUMAR	MANOJ KUMAR SHARMA	FC	AD100844	ASIF MEMON	GROCERY	55	0
FACEWASH	MTD 13TH MAY	MAYANK SINGH	BHATT SACHINKUMAR DUSHYANTKUMAR	MANOJ KUMAR SHARMA	FC	AD100844	ASIF MEMON	GROCERY	55	0
DEEP CLEANSING	MTD 13TH MAY	MAYANK SINGH	BHATT SACHINKUMAR DUSHYANTKUMAR	MANOJ KUMAR SHARMA	FC	AD100844	ASIF MEMON	GROCERY	55	0
DEEP CLEANSING	Jan-20	MAYANK SINGH	BHATT SACHINKUMAR DUSHYANTKUMAR	MANOJ KUMAR SHARMA	FC	AD100844	ASIF MEMON	GROCERY	55	8
DEEP CLEANSING	Feb-20	MAYANK SINGH	BHATT SACHINKUMAR DUSHYANTKUMAR	MANOJ KUMAR SHARMA	FC	AD100844	ASIF MEMON	GROCERY	55	37
DEEP CLEANSING	Mar-20	MAYANK SINGH	BHATT SACHINKUMAR DUSHYANTKUMAR	MANOJ KUMAR SHARMA	FC	AD100844	ASIF MEMON	GROCERY	55	31
DEEP CLEANSING	Apr-20	MAYANK SINGH	BHATT SACHINKUMAR DUSHYANTKUMAR	MANOJ KUMAR SHARMA	FC	AD100844	ASIF MEMON	GROCERY	55	0
FACEWASH	Feb-20	MAYANK SINGH	BHATT SACHINKUMAR DUSHYANTKUMAR	MANOJ KUMAR SHARMA	FC	AD100844	ASIF MEMON	GROCERY	55	43
FACEWASH	Mar-20	MAYANK SINGH	BHATT SACHINKUMAR DUSHYANTKUMAR	MANOJ KUMAR SHARMA	FC	AD100844	ASIF MEMON	GROCERY	55	37
FACEWASH	Apr-20	MAYANK SINGH	BHATT SACHINKUMAR DUSHYANTKUMAR	MANOJ KUMAR SHARMA	FC	AD100844	ASIF MEMON	GROCERY	55	2

Would be grateful for the help.

[manishakar]

Want to have Month-wise % Ratio Vs Jan-Feb Avg

Dears, 

Really need help with given Chart. I have my Column Chart in Power BI but also want to have a Table of Ratio of Month's #ORDERS  Vs Jan-Feb Avg. 

Sample Data is as below:

CATEGORYMONTHREGIONRSMASMSOTEAMISR/DSM IDISR/DSM NAMERETAIL ENVIRONMENTTOTAL OUTLETS#ECO%ECOTOTAL CALLS#ORDERS%PROD.AVG. LINES /OTL# LINESLPPC# LINES2VALUE/OTLVALUE/ ORDERSEC SALESSEC SALES ORDERSTATETOWNTOWN CLASSCOVID-19 ZONE

TOT BUSINESS	Feb-20	WEST	MANOJ KUMAR	SANDEEP S KUKREATI	VARGHIES RAJA NADAR	DC	AD100652	AJAY PANDAY	SELF SERVICE	2	1	50%	8	1	13%	2	2	2	2	82	82	82	82	MAHARASHTRA	BHAYANDER	METRO	RED
TOT BUSINESS	Feb-20	WEST	MANOJ KUMAR	SANDEEP S KUKREATI	VARGHIES RAJA NADAR	DC	AD100652	AJAY PANDAY	SELF SERVICE	5	5	100%	28	8	29%	7.6	38	4.75	38	8,989	5,618	44,945	44,944	MAHARASHTRA	BHAYANDER	METRO	RED
TOT BUSINESS	Jan-20	WEST	MANOJ KUMAR	SANDEEP S KUKREATI	VARGHIES RAJA NADAR	DC	AD100652	AJAY PANDAY	SELF SERVICE	2	2	100%	9	3	33%	3.5	7	2.33	6.99	664	443	1,328	1,329	MAHARASHTRA	BHAYANDER	METRO	RED
TOT BUSINESS	Jan-20	WEST	MANOJ KUMAR	SANDEEP S KUKREATI	VARGHIES RAJA NADAR	DC	AD100652	AJAY PANDAY	SELF SERVICE	5	5	100%	32	10	31%	8.6	43	4.7	47	25721	12860	1,28,605	1,28,600	MAHARASHTRA	BHAYANDER	METRO	RED
TOT BUSINESS	Mar-20	WEST	MANOJ KUMAR	SANDEEP S KUKREATI	VARGHIES RAJA NADAR	DC	AD100652	AJAY PANDAY	SELF SERVICE	2	1	50%	11	1	9%	12	12	12	12	8,893	8,893	8,893	8,893	MAHARASHTRA	BHAYANDER	METRO	RED
TOT BUSINESS	Mar-20	WEST	MANOJ KUMAR	SANDEEP S KUKREATI	VARGHIES RAJA NADAR	DC	AD100652	AJAY PANDAY	SELF SERVICE	5	3	60%	28	3	11%	6.33	19	6.33	18.99	8196	8196	24,588	24,588	MAHARASHTRA	BHAYANDER	METRO	RED
TOT BUSINESS	MTD 13TH MAY	WEST	MANOJ KUMAR	SANDEEP S KUKREATI	VARGHIES RAJA NADAR	DC	AD100652	AJAY PANDAY	SELF SERVICE	2	0	0%	4	0	0%	0	0	0	0	0	0	0	0	MAHARASHTRA	BHAYANDER	METRO	RED
TOT BUSINESS	MTD 13TH MAY	WEST	MANOJ KUMAR	SANDEEP S KUKREATI	VARGHIES RAJA NADAR	DC	AD100652	AJAY PANDAY	SELF SERVICE	5	0	0%	10	0	0%	0	0	0	0	0	0	0	0	MAHARASHTRA	BHAYANDER	METRO	RED

[manishakar]

Dear Experts,

Kindly, help with the solution. 

All the Headers in Sample data have mora than one Unique values. Because of lack of space, I have provided only unique data.

[v-shex-msft]

Hi @manishakar,

You can try to use the following measure formula to find out the first two months from text value and calculate the diff between them:

Measure =
VAR _min =
    MINX (
        ADDCOLUMNS ( ALLSELECTED ( 'Table' ), "Date", DATEVALUE ( "1-" & [MONTHR] ) ),
        [Date]
    )
VAR _next =
    DATE ( YEAR ( _min ), MONTH ( _min ) + 1, 1 )
VAR _diff =
    CALCULATE (
        AVERAGE ( 'Table'[#ECO] ),
        FILTER ( ALLSELECTED ( 'Table' ), [MONTHR] = FORMAT ( _min, "mmm-yy" ) )
    )
        - CALCULATE (
            AVERAGE ( 'Table'[#ECO] ),
            FILTER ( ALLSELECTED ( 'Table' ), [MONTHR] = FORMAT ( _next, "mmm-yy" ) )
        )
RETURN
    _diff

Regards,

Xiaoxin Sheng

[amitchandak]

@manishakar , Try like

Measure =
var _min = date(year(today()),1,1)
var _max = eomonth(date(year(today()),1,1),1)
var _avg=AVERAGEX(FILTER(ALL('Table'),'Table'[Date]>= _min&& 'Table'[Date]<=_max ),'Table'[ECO])

return
calculate(sum('Table'[ECO]) -_avg)

[manishakar]

Hi!

The full  data contains month including Jan-May'20. So, looking to get an average of First 2 months only at TOTAL MONTH level. 

[amitchandak]

@manishakar , try like

Measure =
var _min = date(year(today()),1,1)
var _max = eomonth(date(year(today()),1,1),1)
var _avg=AVERAGEX(summarize(FILTER(ALL('Table'),'Table'[Date]>= _min&& 'Table'[Date]<=_max ),table[Month Year],"_1",sum('Table'[ECO])),[_1])

return
calculate(sum('Table'[ECO]) -_avg)

[nandukrishnavs · Accepted solution]

@manishakar 

Try this

Measure = 
var _avg=AVERAGEX(FILTER(ALL('Table'),'Table'[MONTH]="Feb-20"|| 'Table'[MONTH]="Jan-20"),'Table'[ECO])
var _diff= _avg-CALCULATE(SELECTEDVALUE('Table'[ECO]))
return _diff

 

Did I answer your question? Mark my post as a solution!
Appreciate with a kudos 🙂

[manishakar]

Thanks for quick reply , Bro! I was able to run the Query successfully. However, It's giving Average #ECO of 10.21 at month level which is not right.

FYI, There are multile ISR/DSM & RETAIL ENVIRONMENT in the full document. Your query is probably giving avergae #ECO per ISR/DSM per RETAIL ENVIRONMENT. 

However, I am looking at Average of Total #ECO at Month level.