Create calculation table in PowerBI

[olivierschelstr]

Hi, 

Instead of having to calculate the table (see print screen) on the right side, I would like to have it in a table form in the PowerBI desktop itself. 

I will load the table untill column L and would like to calculate (based on dates) which orders were shipped too late and which ones were on time, both for

- required vs confirmed date

- required vs loaded date

Small remark, I will not add the oclums I J K and L as they are not part of the actual output...

I'm struggling to make this table in PowerBI..

Thank you in advance,

[Anonymous · Accepted solution]

Hello @olivierschelstr ,

Here are the steps you can follow:

1. Create the calculated column and measure, find out. If the order is shipped on time, if the order is delayed, days late

Column:

Req_VS_Conf_Column = IF('Table'[Req.Supplier Delivery Date]>='Table'[Product Ready Confirmation Date],"OK","Nok")

Req_VS_Load_Column = IF('Table'[Req.Supplier Delivery Date]>='Table'[Loadingdate],"OK","NOK")

Measure:

days_late_Req_Ready = DATEDIFF(MIN('Table'[Req.Supplier Delivery Date]),MAX('Table'[Ready as from/at Ardo site]),DAY)

days_late_Req_Loading = DATEDIFF(MIN('Table'[Req.Supplier Delivery Date]),MAX('Table'[Loadingdate]),DAY)

Result:

2. Create a measure, find total orders, OK orders, NOK orders, service level, average days late.

Total Orders = COUNT('Table'[Req_VS_Conf_Column])

Req_Conf_Service_Level = DIVIDE(CALCULATE(COUNT('Table'[Req_VS_Conf_Column]),FILTER('Table','Table'[Req_VS_Conf_Column]="OK")),COUNTX(ALL('Table'),[Req_VS_Conf_Column]))

Req_conf_Average_days_late = SUMX(ALL('Table'),'Table'[days_late_Req_Ready])/COUNTX(ALL('Table'),'Table'[days_late_Req_Ready])

Req_Load_Service_Level = DIVIDE(CALCULATE(COUNT('Table'[Req_VS_Load_Column]),FILTER('Table','Table'[Req_VS_Load_Column]="OK")),COUNTX(ALL('Table'),[Req_VS_Load_Column]))

Req_Load_Average_days_late = SUMX(ALL('Table'),'Table'[days_late_Req_Loading])/COUNTX(ALL('Table'),'Table'[days_late_Req_Loading])

Result:

You can download the PBIX file from here.

Best regards

Liu Yang

If this post helps,then consider Accepting it as the solution to help other members find it faster.

[amitchandak]

@olivierschelstr , as you right side table is basically a split of the measure only. You need to have a calculated table for disply.

Example

union(
summarize("Measure","Share of Voice", "YTD",[Share of Voice YTD], "LYTD",[Share of Voice LYTD], "Change %",[Share of Voice %]),
summarize("Measure","SOV Rank", "YTD",[SOV Rank YTD], "LYTD",[SOV Rank LYTD], "Change %",[SOV Rank %])
)

I thought about, show on the row on Matrix. But that is for One column that can values like what shown