Syntax for Calculation

[george456]

Dear Community

I have a challenge and need some assistance.  

The following Scenario Applies:  I have product Structure file and and Fact File. On the fact file I need to multiply the values of the cases sold by the dimensions on the product structure file,this dimension I called dimension . This will give me Units.(Dimension*[Cases Sold]) as Units

However when I want to use a variable to toggle between Value Sold , Cases Sold and Units the units Calculation is not giving me any output.

I was thinking of doing this calc in the loading script but even that is not working. any Suggestions?

regards

[Arthur_Fong]

You have a sample app?

[george456]

I can only share the attached. I hope this is workable

[Arthur_Fong]

No data rows are available in the sample provided.

Can you please mock up some of the data?

[george456]

Hi Arthur.

Mocked up some dummy data.  I trust this will be acceptable

Thanks

[Arthur_Fong]

I toggled around with your list and i see output are changing. Can you please explain more what do you want to achieve?

 

[george456]

Hi

Thanks, yes those are working. What I need to achieve is to calculate units.

units will be a function of (Dimensions*[Cases Sold]) if I try and declare a variable I get incorrect output.

Hope this is clear

Regards

[Arthur_Fong · Accepted solution]

Try this:

=if($(vMeasure)=[Cases Sold],sum([Cases Sold]),
if($(vMeasure)=[Value Sold],sum([Value Sold]),sum(total DISTINCT Dimensions)*sum([Cases Sold])))