Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 
shane_spencer
Specialist

Count of Mode of 3 out of 4 dimensions

I have 4 Dimensions in a table [Lock Local Period], [TDW Legal Entity Code], [TDW Amz Reg Jurisdiction] and [TDW Amz Reg] (the field [Full Legal Entity Name] can be ignore as it's the long name of [TDW Legal Entity Code]). See screenshot.

I am trying to create some Measures about [TDW Amz Reg] based on the following 3 dimensions [Lock Local Period], [TDW Legal Entity Code], [TDW Amz Reg Jurisdiction]. For example the (most frequent) Mode [TDW Amz Reg] for [Lock Local Period], [TDW Legal Entity Code], [TDW Amz Reg Jurisdiction] and the Count of the Distinct [TDW Amz Reg] by [Lock Local Period], [TDW Legal Entity Code], [TDW Amz Reg Jurisdiction]. i.e.  

 

=aggr( Mode([TDW Amz Reg]), [Lock Local Period], [TDW Legal Entity Code], [TDW Amz Reg Jurisdiction])
Count(Distinct TOTAL <[Lock Local Period], [TDW Legal Entity Code], [TDW Amz Reg Jurisdiction]> [TDW Amz Reg])

 

So far so good.  Now I want to count of how frequently the Mode [TDW Amz Reg] per [Lock Local Period], [TDW Legal Entity Code], [TDW Amz Reg Jurisdiction] appears. I'm struggling because I don't think Aggr will work with having the [TDW Amz Reg] as a column.  

Count Mode.png

Labels (1)
0 Replies