Forum Discussion
Help With Measure
I have a line chart (screenshot below) with two measures and I need help with the 2nd measure:
- %on-time - calculates the number of items that were delivered on time (green line chart below)
- %on-time (normalization applied) - calculates the % of items that were delivered on-time based on the distribution/ratio* between Product Type (bulk, clearance) and Address Type (box, urban)
*distribution/ratio based on the following:
Base Distribution
| Bulk | Clearance | |
| Box | 11% | 28% |
| Urban | 89% | 72% |
Ratio to Bulk, Clearance
| Bulk | 75% |
| Clearance | 25% |
Final Distribution
| Bulk | Clearance | |
| Box | 8.3% (=0.11*0.75) | 7.0% (=0.28*0.25) |
| Urban | 66.8% (=0.89*0.75) | 18.0% (=0.72*0.25) |
For the 2nd measure mentioned above, %on-time (normalization applied), the result represented by blue line below is normally higher than the %on-time measure (green line) so I believe my logic needs scrutinising.
%OnTime(Norm applied) = ([%OnTimeBox-Bulk]*[FinalDistBox-Bulk])+([%OnTimeUrban-Bulk]*[FinalDistUrban-Bulk])+([%OnTimeBox-Clearance]*[FinalDistBox-Clearance])+([%OnTimeUrban-Clearance]*[FinalDistUrban-Clearance])
Appreciate any help. Thanks.
16 Replies
Anonymous
%OnTime = [TotalOnTime]/[TotalAccep&DelScan] [TotalOnTime] = CALCULATE(COUNTROWS('DistProfile v1-3'),FILTER('DistProfile v1-3','DistProfile v1-3'[Acceptance]<>BLANK()&&'DistProfile v1-3'[Delivery]<>BLANK()&&'DistProfile v1-3'[On Time]="Y")) [TotalAccep&DelScan] = CALCULATE(COUNTROWS(FILTER('DistProfile v1-3','DistProfile v1-3'[Delivery]<>BLANK()&&'DistProfile v1-3'[Acceptance]<>BLANK()&&'DistProfile v1-3'[On Time]<>"Err Scan"&&'DistProfile v1-3'[On Time]<>"No Scan")))
Anonymous
Conceptually, is %on-time (normalization applied) a weighted average of the components that make up %on-time?? With the weights determined by distribution/ratio? If not, what is it?
Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
AlB yes that is correct
Anonymous
Ok, I'm just trying to understand. Then
1. Why don't those components show up in the code for [%on-time]?
2. Could then [%on-time] be calculated as
(([%OnTimeBox-Bulk])+([%OnTimeUrban-Bulk])+([%OnTimeBox-Clearance])+([%OnTimeUrban-Clearance]))*0.25
i.e. with equal weigth for those components ?
Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
AlB fair point however with your edited measure, there's no inclusion for the 75% distribution/ratio for Bulk?
Anonymous
The 0.25 is just because there are four. It doesn't have to do with Clearance or bulk. I'm just saying if [%on-time] is built of those 4 components with equal weight for each of them, then it should be able to be calculated as
([%OnTimeBox-Bulk])+([%OnTimeUrban-Bulk])+([%OnTimeBox-Clearance])+([%OnTimeUrban-Clearance]))*0.25
where the 0.25 is the equal wight for all four of them
I'm trying to see if there's an inconsistency in the results there
Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
AlB i see what you mean now and sorry for the confusion. The weightings are based on the Final Distribution table (included in initial post). Hope that helps clarify..
Anonymous
I'm not trying to dispute the formula. If %on-time (normalization applied) is a weighted average of the components that make up %on-time like you've said, I repeat the questions:
1. Why don't those components show up in the code for [%on-time]?
2. Could then [%on-time] be calculated as
(([%OnTimeBox-Bulk])+([%OnTimeUrban-Bulk])+([%OnTimeBox-Clearance])+([%OnTimeUrban-Clearance]))*0.25
Why is the formula on 2 incorrect? If %on-time (normalization applied) is the weighted average of those components, then [%on-time] could be calculated with an equal weight of those components. And if we do that, the result is different from what you get in your [%on-time] but looks closer to [%on-time (normalization applied)]. So that can perhaps give you an idea on where to look further... The largest of those components is [%OnTimeUrban-Bulk] but that only hovers around 60%. How come a weighted average of those components then yields the result you have for [%OnTime], which hovers around 80%+?
Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
AlB no my apologies, the 1st measure %on-time is the (raw) result that only counts the number of items where [ontime]="Y". Also note, there is no weighting (distribution/ratio) applied to this measure.
Here is snapshot of both measures and what the expected %on-time (normalisation applied) result should be, highlighted in table below. This is based on Excel formula and I've tried to replicate in dax (measure included at top of this post) but obviously I'm doing something wrong.
Anonymous
If you have the expected values for [%ontime (norm applied)] as you show above, I assume you have the expected values of those 4 components as well. So it would be a matter of checking them one by one against the measures you have. I'm not familiar with the underlying formulae involved, so I cannot really help much unless you explain what the measures should return and how and then show the code you are using to get there.
Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
Hi AlB , here is link to folder that contains both the pbix and Excel file.
Anonymous
What exactly would you like me to do with those files?
Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
Hello Anonymous,
Add the weights as a calculated column and use it in the calculation. Instead of just counting rows you count the weighted column instead which will be between 0 and 1.
Either create a relationship and just use RELATED(), or do something with SELECTEDVALUE() and lots of filters to get the correct values in the column.
Assuming that your calculation looks something along the lines of this:
%OnTimeBox-Bulk =
CALCULATE( COUNTROWS( Deliveries ) , Status = "On time" , Dim1 = "Bulk" , Dim2 = "Box" ) /
COUNTROWS( Deliveries)
You want to make the measure into this:
%WeightedOnTimeBox-Bulk =
SUMX( FILTER(Deliveries , Status = "On-time" && Dim1 = "Box" && Dim2 = "Bulk" ) , [Weight]) /
SUMX( Deliveries , [Weight] )
Let me know how it goes. I might not be to active during christmas times so PM me on linkedin if I'm not responding!
Br,
J
