Measure: show dcount as % by date hierarchy

[crojas]

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

• On Time - Column is the result of the following calculation: (which i added in Power Query using M while transfoirming my data)

= 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") 

• On Time (groups) - just groups early and on time for practical purposes my company considers both as acceptable, so if the sum of both is greater than 70% everything is ok.

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?

[daxer-almighty]

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.

[crojas]

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?