Forum Discussion
Create calculation table in PowerBI
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,
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.
2 Replies
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
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.
