PowerPivot and DAX papers,posts,FAQs,webcasts,samples

I'm having trouble figuring out this DAX stuff. I know there's a simple answer here but I can't figure it out...

[K1Code] table
K1Code-----AllocCode
ORDINC-----WBC
LTCGL-------CGL
INTINC-------WBC

[AMOUNT] table
EntityID-----K1Code-----Amount
B-LIQ-------ORDINC-----9500
B-LIQ-------LTCGL-------25900
B-LIQ-------INTINC-------3400

[RATIOS] table
EntityID-----PtrID-----AllocCode-----AllocPct
B-LIQ--------ABC------WBC-----------56%
B-LIQ--------XYZ-------WBC-----------23%
B-LIQ--------GUY------WBC-----------21%
B-LIQ--------ABC------CGL------------75%
B-LIQ--------XYZ-------CGL------------10%
B-LIQ--------GUY------CGL------------15%


What I'm trying to do is create a Pivot table with PtrID on the Rows Label, K1Code on the Columns label, and the Values be an expression multiplying the PtrID's AllocPct and the K1Code Amount based on the AllocCode assigned to each. So for example, the value where Row PtrID = ABC and Column K1Code = LTCGL should be 19425.

Can anyone help me come up with a calculated measure to do this? I keep running into context errors.

Thanks!

Logan could you please clarify relationships between tables? It is clear that Amounts is related to K1Code on K1Code. Is table Ratios related to Amount table just on EntityID?

There's no relationship between RATIOS and AMOUNT tables.

The only relationship is AMOUNT[K1Code]-->K1CODE[K1Code] as you have pointed out.

Also there is a Calculated Column [AllocCode] in AMOUNT that is =RELATED(K1CODE[AllocCode]).

Does that help?

Hi Logan,

Very interesting problem you have here, as you don't have relationships between tables.

Lets first create measure AvgAllocPct in the Amounts table with following DAX formula:



I need this calculated measure to deal with cases when more than one row with AllocPct could be returned.

Now lets create our calculated measure with following DAX formula:


Could you please try this and see if that works for you? Please let me know - I would be very interested to hear if that works as you expected.

Regards,

Vidas Matelis