Hi I am trying to return values from a 'Rates' table linked to the below 'MU' table on the Parent dimension (one MU to Many Rates)
I am using the formula SUMX( VALUES ( MU[Parent] ) ,[Allocation %]) where Allocation% is a sum of the 'Rates' table values.
This works fine, however, in some instances there will not be a value for MU([Parent]) in the 'Rates' table. In this instance I would like to return the values linked to the 'Dept' column instead (which will neccessitate a many to many inactive relationship).
I am unable to successfully write a measure which substitues this 'Dept' link and then still consolidates the values correctly at total level.
I have tried something such as the below where I try and check whether the value exists and then substitute if not, and this works for individual rows but the totals are incorrect.
Have been stuck almost a full day on this, any help you can offer would be greatly appreciated. Thanks in advance.
VAR hasvalue=CALCULATE([Allocation %],ALL(Rates[Stage],Rates[TA],Rates[Project Code]))
CALCULATE([Allocation %],USERELATIONSHIP(MU[Dept],Rates[MU Code]),CROSSFILTER(MU[Parent],Rates[MU Code],None))
Link to demo file....https://drive.google.com/file/d/18PllpMYNDgzm3ck9pThhFGrCkZu78bmi/view?usp=sharing