Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hey everyone, i've just started in power bi and im having trouble with some calculations, for starters i just nee to calculate and graph the On Time KPI foe my company deliveries.
I have scheduled delivery date and time with low and high limits as first set of data, an then i have the real delivery date and time, everything identified by a unique delivery ID.
I basically need to calculate compliance % in 3 levels (Early, on time, and late) and graph them by date hierarchy.
My data structure is as follows:
Guide | Min Time of Arrival | Max Time of Arrival | Arrival Time | On Time | On Time (groups) |
12345 | 11/11/2020 06:00 | 11/11/2020 07:00 | 11/11/2020 05:00 | Early | On Time |
23456 | 11/11/2020 06:00 | 11/11/2020 07:00 | 11/11/2020 06:30 | On Time | On Time |
34567 | 11/11/2020 06:00 | 11/11/2020 07:00 | 11/11/2020 14:00 | Late | Late |
= Table.AddColumn(#"Extract - Hora Creación", "On Time", each if [Tiempo Llegada] < [Tiempo Min Entrega] then "Anticipado" else if [Tiempo Llegada] > [Tiempo Max Entrega] then "Retraso" else "On Time")
I tried the following dax formula for the measure im trying to graph and it does.
On Time KPI = CALCULATE(COUNT(Beetrack[On Time]),ALLEXCEPT(Beetrack,Beetrack[Tiempo min entrega].[Día],Beetrack[On Time])) / CALCULATE(COUNT(Beetrack[On Time]),ALLEXCEPT(Beetrack,Beetrack[Tiempo min entrega].[Día]))
The problem here is the resulting graphs cant be affected by any filter, and cant be drilled down/up by any date hierarchies, so i cant show monthly nor annual nor weekly values. I also cant highligt the same data when i click on other graphs in the dashboard to segment the data.
Is there any other way to achieve the same calculation dynamically? so i can get the values by date hierarchies? and that the graph is not always static?
Of course, everything is possible... as long as the model is correct. Your model is not. Please read this to know how to create such models. Then the calculations and navigation will be a breeze.
Also, I would be very careful using ALLEXCEPT. This is a very deceiving function and you should really understand how it behaves in contexts to use it. Please go to www.sqlbi.com and find the artictle that explains the pitfalls/caveats of the function.
I now understand that my model is incorrect, but we are using excel reports generated by SAP and other APP as origin data bases, so tables in the reports are very big in both rows and columns, I am currently using 3 fact tables with no dim tables, i added an extra date table
All 3 tables we are using are fact tables, and inside each of them is data (ID keys (DIM) and description (FACT)) from other SAP tables which we dont have access
I need to restructure my model, does this means i would need to split my big tables into small dim ones??
Do you have any tips or recomendations on how to do this inside BI? or how to put together a star schema with multiple fact tables?
Im Sorry for the trouble but i am recently starting and i cant manage to do this first.
Any recomended resources i can check on data modelling? or POwer bi Trainings?
User | Count |
---|---|
60 | |
22 | |
18 | |
18 | |
16 |
User | Count |
---|---|
87 | |
59 | |
52 | |
38 | |
21 |