Project Desc - MI Installation done in different towns
Link for PBIX - https://drive.google.com/file/d/1RyWpmuGxZ0t5XQz89TvPYo0GrPMxCldP/view?usp=sharing
Data Model - Two Type of MI Tables (Fact Table) are there - MI Consumer & MI DTR
Help Required In -
A. Report Page - ‘MI Report’
-
All Days not showing - In the Visual ‘Meter Installation’ when we drill down to days - we are seeing only days when MI has been done, but it should show all days in the month and where there is no Mi figure should reflect as ‘0’
-
Incorrect Average MI - It is also showing incorrect, as in Metrics is dividing the Total MI with only days where there are installation, but it should also count days where there is no installation, with ‘0’ count’
B. Report Page - ‘MI Report 2’
-
In Visual - ‘cum MI by Date’ - In Cumulative there is no MI after date current date i.e. 05-05-2023, as such the visual should only show date / year till current date and not for entire Calendar Date Table.
C. Report Page - ‘MI Report 3’
4) Calculation of Acutal Achieved Date - in this milestone table, we need to plot the date on which actual MI of milstone has been acheived. The Total MI is the count of rows from 2 tables - ‘MI Consumer’ & ‘MI DTR’. We shall have to append these two table with selcted columns and count rows until milestone qty is acheived, and track the miinimum date in this visual. However i am unable to do so and shall need some help in this regards.
Shall really appreciate if somebody can help me out in this.
Regards,
Vikash M
Would changing the average measure to force a zero give the results you are looking for:
Avg MI = AVERAGEX(KEEPFILTERS(VALUES(‘Calendar’[Date])), VAR MI_Cons = [MI Consumer] RETURN IF(ISBLANK(MI_Cons),0,MI_Cons))
I couldnt see anything on report 2 page, but my thoughts are the cumulative pattern would require a condition to be added to the filter that the date is less than current date, that would add a constant line after the date, if you wanted to blank the measure after the current date then you would need to force the measure to return blank, you can use an if statement to do that, or I think you could divide the amount by the logical test current date >= value, the logical math of false being zero would cause a divide by zero error causing a blank to be returned by divide.
There was no report 3 page I could see, my first thoughts were power query if you want to append the tables, or maybe better to take a copy of your date from your date table if in powerquery, and then merge the appended MI Tables on the date, expand and then use group by and count rows, you then would have a table of dates and totals to use of the count of rows by date in Power Bi.
But you could take the values of the date in powerbi, and use add column and the two measures you have in the model to count rows of each table and create either a physical or virtual table to calculate the milestone per day.
If you add a disconnected table of the mile stone dates and values and use them to populate a table/matrix visual, you could use the selected value of each row to filter the above tables by the mile stone value and calculate the max date.
Would be really interesting to hear others approaches to this, as these are just my thoughts on how I would start to approach it.
Hello @vikashmukim
Did the response above from @kylie.oconnell help solve your query?
If not, can you let us know where you’re stuck and what additional assistance you need?
If it did, please mark the answer as the SOLUTION by clicking the three dots beside Reply and then tick the check box beside SOLUTION
Thank you
Hello @vikashmukim
We’ve noticed that no response was received from you on the post above.
Just following up if the response above help you solve your inquiry.
If it did, please mark his answer as the SOLUTION.
In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved.
Sorry, for delay in closing this topic, yes i have received my solution, thanks much.
Really appreciate the promptness of your team in resolving issues.
Thanks.