Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I need to do an analysis and I'm struggling with it. Basically I have three important things on my database: the date, the distribution center code and the vehicle's plate. I need to calculate the percentage of days that a specific vehicle shows up on the distribution center and classify them according to it (over 60% the vehicle is considered reliable and below that is not). I'm struggling with the fact that a vehicle can show up to multiple centers and on different months and the distribution centers have a different total of operating days.
For example. On January, the DC #4 was open in 30 days but the DC #5 opened for 20 days. If a vehicle that shows up to the DC 4 15 days is not reliable for that center (15/30 = 50%) and its reliable for DC 5 if it shows up for the same number of days (15/20 = 75%).
I've tried to do a reference table and do some distinctcounts but I'm struggling with it, since I need to count the number of operating days for a center in a specific month and the number of days the vehicle showed up in a specific center in a specific month. Is there an easier way to do it? In a dream scenario I would have this table below, so I could count how many reliable vehicles I had in a month:
Center Month Number of operating days Plate #Days Vehicle Showed Up % Of Appearances Type
400 01 25 ABC123 20 80% Reliable
400 02 30 ABC123 18 60% Reliable
Reminder: the database has a "random" number of lines for each cmbination of day-plate-distribution center (it depends on how large was the order and etc). I've done it in Excel and its much easier, but unfortunately I cant change the database.
I don't know if I'm being clear, please let me know. Thank you in advance
Solved! Go to Solution.
Hi @Anonymous ,
We add some fake data into yours to have expected data, if using orgin data, it will get result 20/20 = 100%.
then we can just create a calculated table to meet your requirement:
Test = ADDCOLUMNS ( ADDCOLUMNS ( ADDCOLUMNS ( GROUPBY ( 'Table', 'Table'[Distribution Center], 'Table'[Date].[Year], 'Table'[Date].[MonthNo], 'Table'[Plate] ), "Number of operating days", COUNTROWS ( DISTINCT ( SELECTCOLUMNS ( FILTER ( 'Table', [Distribution Center] = EARLIER ( [Distribution Center] ) && YEAR ( [Date] ) = EARLIER ( 'Table'[Date].[Year] ) && MONTH ( [Date] ) = EARLIER ( 'Table'[Date].[MonthNo] ) ), "DateCount", [Date] ) ) ), "#Days Vehicle Showed Up", COUNTROWS ( DISTINCT ( SELECTCOLUMNS ( FILTER ( 'Table', [Distribution Center] = EARLIER ( [Distribution Center] ) && YEAR ( [Date] ) = EARLIER ( 'Table'[Date].[Year] ) && MONTH ( [Date] ) = EARLIER ( 'Table'[Date].[MonthNo] ) && [Plate] = EARLIER ( 'Table'[Plate] ) ), "DateCount", [Date] ) ) ) ), "% Of Appearances", DIVIDE ( [#Days Vehicle Showed Up], [Number of operating days], 0 ) ), "Type", IF ( [% Of Appearances] >= 0.6, "reliable", "not reliable" ) )
but if you want the calculated column in origin table, we can use the following formula:
Number of operating days = COUNTROWS ( DISTINCT ( SELECTCOLUMNS ( FILTER ( 'Table', [Distribution Center] = EARLIER ( [Distribution Center] ) && YEAR ( [Date] ) = EARLIER ( 'Table'[Date].[Year] ) && MONTH ( [Date] ) = EARLIER ( 'Table'[Date].[MonthNo] ) ), "DateCount", [Date] ) ) )
#Days Vehicle Showed Up = COUNTROWS ( DISTINCT ( SELECTCOLUMNS ( FILTER ( 'Table', [Distribution Center] = EARLIER ( [Distribution Center] ) && YEAR ( [Date] ) = EARLIER ( 'Table'[Date].[Year] ) && MONTH ( [Date] ) = EARLIER ( 'Table'[Date].[MonthNo] ) && [Plate] = EARLIER ( 'Table'[Plate] ) ), "DateCount", [Date] ) ) )
% Of Appearances = DIVIDE ( [#Days Vehicle Showed Up], [Number of operating days], 0 )
Type = IF ( [% Of Appearances] >= 0.6, "reliable", "not reliable" )
BTW, pbix as attached.
Best regards,
Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Anonymous ,
Sorry for that, We cannot understand your data model clearly, Could you please provide a sample mockup table based on the expected result table you have shared or describle the fields of each tables and the relations between tables simply if don't have any Confidential Information.
Best regards,
Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
I'm really sorry. I could not figure out how to put my excel sampla here. So I had to take a printscreen of it. Basically what I have in my data base is the following:
What I need to do: a distinctcount of dates from a month for each distribution center (to see how many days they opened in January, for example) and a distinctcount of dates that a specific vehicle showed up to that specific distribution center in each month. So I would like to have a column that repeated the number of days that the center opened in that month (in January, the distribution center 100 worked 30 days, so all lines from it and January would have 30) and a column that repeated the number of days that the vehicle showed up to that center (the ABC123 showed up 20 days of january). If I could do that, would be a matter of dividing 20/30 for every row with both conditions and it would return 0,66 (or 66%). What I need to do is classify that percentage with an if (if x% >= 0,6; "reliable"; "not reliable") in order to count how many plates are reliable and how many are not for each month.
I hope that helps. Please, do you have any idea on how to do it? Thank you in advance!
Hi @Anonymous ,
We add some fake data into yours to have expected data, if using orgin data, it will get result 20/20 = 100%.
then we can just create a calculated table to meet your requirement:
Test = ADDCOLUMNS ( ADDCOLUMNS ( ADDCOLUMNS ( GROUPBY ( 'Table', 'Table'[Distribution Center], 'Table'[Date].[Year], 'Table'[Date].[MonthNo], 'Table'[Plate] ), "Number of operating days", COUNTROWS ( DISTINCT ( SELECTCOLUMNS ( FILTER ( 'Table', [Distribution Center] = EARLIER ( [Distribution Center] ) && YEAR ( [Date] ) = EARLIER ( 'Table'[Date].[Year] ) && MONTH ( [Date] ) = EARLIER ( 'Table'[Date].[MonthNo] ) ), "DateCount", [Date] ) ) ), "#Days Vehicle Showed Up", COUNTROWS ( DISTINCT ( SELECTCOLUMNS ( FILTER ( 'Table', [Distribution Center] = EARLIER ( [Distribution Center] ) && YEAR ( [Date] ) = EARLIER ( 'Table'[Date].[Year] ) && MONTH ( [Date] ) = EARLIER ( 'Table'[Date].[MonthNo] ) && [Plate] = EARLIER ( 'Table'[Plate] ) ), "DateCount", [Date] ) ) ) ), "% Of Appearances", DIVIDE ( [#Days Vehicle Showed Up], [Number of operating days], 0 ) ), "Type", IF ( [% Of Appearances] >= 0.6, "reliable", "not reliable" ) )
but if you want the calculated column in origin table, we can use the following formula:
Number of operating days = COUNTROWS ( DISTINCT ( SELECTCOLUMNS ( FILTER ( 'Table', [Distribution Center] = EARLIER ( [Distribution Center] ) && YEAR ( [Date] ) = EARLIER ( 'Table'[Date].[Year] ) && MONTH ( [Date] ) = EARLIER ( 'Table'[Date].[MonthNo] ) ), "DateCount", [Date] ) ) )
#Days Vehicle Showed Up = COUNTROWS ( DISTINCT ( SELECTCOLUMNS ( FILTER ( 'Table', [Distribution Center] = EARLIER ( [Distribution Center] ) && YEAR ( [Date] ) = EARLIER ( 'Table'[Date].[Year] ) && MONTH ( [Date] ) = EARLIER ( 'Table'[Date].[MonthNo] ) && [Plate] = EARLIER ( 'Table'[Plate] ) ), "DateCount", [Date] ) ) )
% Of Appearances = DIVIDE ( [#Days Vehicle Showed Up], [Number of operating days], 0 )
Type = IF ( [% Of Appearances] >= 0.6, "reliable", "not reliable" )
BTW, pbix as attached.
Best regards,
Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thank you so much 🙂
I do have some confidential informations, but I'll try my best to simulate my data base with some fake data. I just need a couple of minutes 🙂
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
135 | |
117 | |
101 | |
71 | |
61 |