Help With Measure

[Anonymous]

I have a line chart (screenshot below) with two measures and I need help with the 2nd measure:

1. %on-time - calculates the number of items that were delivered on time (green line chart below)
2. %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])

Link to file fyi.

Appreciate any help. Thanks.

[tex628]

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

[AlB]

@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 

[Anonymous]

Hi @AlB , here is link to folder that contains both the pbix and Excel file.

[AlB]

@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 

[Anonymous]

The Excel file shows the %on-time (normalisation applied) formula and the pbix file contains the replica formula in dax, also provided below. The purpose of the files (specifically Excel) is to provide context to the dax measure I'm having issues with.

%OnTime(Norm applied) = ([%OnTimeBox-Bulk]*[FinalDistBox-Bulk])+([%OnTimeUrban-Bulk]*[FinalDistUrban-Bulk])+([%OnTimeBox-Clearance]*[FinalDistBox-Clearance])+([%OnTimeUrban-Clearance]*[FinalDistUrban-Clearance])

[Anonymous]

It has been suggested to me to tag fellow super users to help with my issue if possible please. Any help is much appreciated. Thank you in advance @mahoneypat , @Fowmy , @amitchandak , @wdx223_Daniel 

[AlB]

@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 

[Anonymous]

@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.

[AlB]

@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 

[Anonymous]

@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..

[AlB]

@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 

[Anonymous]

@AlB  fair point however with your edited measure, there's no inclusion for the 75% distribution/ratio for Bulk?

[AlB]

@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 

[Anonymous]

@AlB  yes that is correct

[Anonymous]

whats the formula for your first measure??

[Anonymous]

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