Forum Discussion
90 days Utilization
Hi,
Please find the attached sheet as sample data, please help me with same results in BI Please
14 Replies
Anonymous - Perhaps this will help: https://community.powerbi.com/t5/Community-Blog/Excel-to-DAX-Translation/ba-p/1060991
hi Anonymous
I don't see any expected output in this excel file:
Please share your expected output in excel, that way, we will give you further help in power bi.
Please see this post regarding How to Get Your Question Answered Quickly:
https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490Regards,
Lin
Hi v-lili6-msft
Sorry wrong fike postedAlso see the results below and there are graphs in file, i wish to be have same graph as well
Please help
hi Anonymous
First, there is a mistake in your excel file.
=IF([@Day] = "Mon", [@WeekOf], IF([@Day] = "Tue", [@WeekOf]+1, IF([@Day] = "Wed", [@WeekOf]+2, IF([@Day] = "Thur", [@WeekOf]+3, [@WeekOf]+4))))
In your Day column, it is Thu not Thur.
please adjust it and refresh the excel file.
Second, then create a 90% Percentile (90 Days) column by this formula:
90% Percentile = var _90date=MINX(TOPN(91,FILTER(Table1,Table1[Actual Date]<EARLIER(Table1[Actual Date])),Table1[Actual Date],DESC),Table1[Actual Date]) return CALCULATE(AVERAGE(Table1[Avg Weekly]),FILTER(Table1,Table1[Actual Date]<EARLIER(Table1[Actual Date])&&Table1[Actual Date]>=_90date))*0.9Result:
and here is sample pbix file, please try it.
Regards,
Lin
hi Anonymous
Do you mean Avg Weekly is a measure? I couldn't see Daily occu column too.
From the excel file, you must have a column to create a measure,
it is better you could share your sample pbix file for us have a test. that will be a great help.
Regards,
Lin
Hi v-lili6-msft
Thanks you so much for your greate help
Please have a look attached data in which iave give Pine Code, Date, Cust #.
wish to be have weekly average cust# count, Monthly average Cust# count and its 90days pecentile (90 days) like you created but it should be measure if possible
Thanks in advance
Revised Sample Datahi Anonymous
If you want a meausre, adjust the formula as below:
Measure 2 = VAR _90date = MINX ( TOPN ( 91, FILTER ( ALL ( Table1 ), Table1[Actual Date] < MAX ( Table1[Actual Date] ) ), Table1[Actual Date], DESC ), Table1[Actual Date] ) RETURN AVERAGEX ( FILTER ( ALL ( Table1 ), Table1[Actual Date] < MAX ( Table1[Actual Date] ) && Table1[Actual Date] >= _90date ), [Avg Weekly] ) * 0.9and this will lead a measure total problem for only month row context, so add a new measure as below:
Measure 3 = SUMX(VALUES(Table1[Actual Date]),[Measure 2])See this post about it
https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907Regards,
Lin
I have shared sample data which i am refering in my report, Please help me have same result
wish to be have weekly average cust# count, Monthly average Cust# count and its 90days pecentile (90 days) like you created but it should be measure if possible
Thanks in advancelike i have in excel
Please
hi Anonymous
the sample data is so big and it is raw data, could you please share a simple pbix file and your expected output.
Regards,
Lin
I have shared sample data which i am refering in my report, Please help me have same result
wish to be have weekly average cust# count, Monthly average Cust# count and its 90days pecentile (90 days) like you created but it should be measure if possible
Thanks in advancelike i have in excel
Please
