Dimension Selection Behaviour

[reanfadyl]

Hi All,

I am just working through a problem create a chart to compare the variance of 'sales enquiries' vs final sales amounts. If someone can help with ideas on how to adjust the chart to show the correct results when the selections below are made that would be great.

I have attached a simplified mock-up of the app.

Desired Results on selecting Consultant dimension:

1. Select Bob - Nothing appears in Enquiry vs. Sale Variance - he has made no Sales .

2. Select Pauline Or Sue - The variance for the TOTAL sale for Enquiry 5 remains 0 even though individually it is $250

Cheers

Rean

[sunny_talwar]

I am not sure I follow what you want when we select Pauline or Sue. Can you elaborate?

[Gysbert_Wassenaar · Accepted solution]

Perhaps these expressions do what you want:

Sales Amount: Sum({<ConsultantName, FactSource = {'Sales'} >} Amount)

Enquiry Amount: Sum({<ConsultantName, FactSource = {'Enquiry'}>} Amount)

[reanfadyl]

Thanks Gysbert,

By combining your Formula's with the below as a calculated dimension I got the result I was after. 

=Aggr(Concat(Distinct {< FactSource = {'Sales'} >} ConsultantName, ' ; '),EnquiryID)

A couple of follow up questions:

A) Is there any better way to get the selection behavior I was after than the calculated dimension above.  I am a little worried about the Calculated Dimension performance overhead (maybe unnecessarily?)

B) How would you recommend explaining to consumers of the report that the results per Sales person are non-additive e.g. Sue and Pauline shouldn't think they have both made a $500 sale.

Regards

Rean

[Gysbert_Wassenaar]

I don't see why you need that calculated dimension. What does it add that the expression Concat({< FactSource = {'Sales'} >} ConsultantName, ' ; ') doesn't?

A) see above, it looks like an unnecessary dimension to me.

B) Perhaps show their contribution to it either in dollars or percentage.

[reanfadyl]

Hi Gysbert,

A) I added the calculated dimension so if Bob was selected then no rows would be shown.  Is there a better way to do this?

B) Very good suggestion, thank you.

[Anonymous]

Sales Amount: Sum({<ConsultantName, FactSource = {'Sales'} >} Amount)

Enquiry Amount: Sum({<ConsultantName, FactSource = {'Enquiry'}>} Amount)