Data visualization with multiple values to plot

[Anonymous]

Hi,

I have data, to discuss here, extracted to excel which i could not attach here, but extract of fields needed for this query are in picture attached below. Just to brief, there are two measures, OTD (On-Time delivery) new measure and OTD old measure calculated based on date an order is placed to supplier and item on the order is received. This is KPI for supplier.

 

Values in both OTD columns will be either of below based on state (in AUS) and delivery duration.

Closed Outside time
Closed On Time
Error
Controlled
Open with ETA outside time

Below is extract from pivot of above data. For ex, 4.26% of total order lines in sep 2019 were delivered on time based on new OTD and 8.61% based on old OTD.

I have to compare these two measure, highlighted in green in pivot above, in a visualisation (prefarably line chart) in Power BI for each month. I have to compare the percentage of "Closed On Time" lines (can be count of ITEMNUM) from both measures for each month. Something similar to below

Please help.

[v-frfei-msft · Accepted solution]

Hi @Anonymous ,

In your scenario , we can use CALENDARAUTO or CALENDAR to create a date table and make it related to the facted tables.

[Anonymous]

Hi @v-frfei-msft,

I have created Date Table using CALENDAR, but heirarchy of date field from fact tables have been lost.

Could you please suggest if heirarchy can still be retained in fact tables, because Date Table has been linked to few fact tables and date range is different in each fact table and when i use date from Date Table, visualisation shows all dates from Date Table. But i want dates from fact table.

[amitchandak]

You can create a date table and join with that.  And have month year there and plot on that

You can create sun total

https://community.powerbi.com/t5/Desktop/Percentage-of-subtotal/td-p/95390

% of total

https://www.linkedin.com/pulse/power-bi-calculate-percentage-based-column-total-madan-bhintade