Measures with tables related by multiple fields

[AFAmores]

Hi!

I have two tables ("a10" and "a64") related by multiple fields ([geo], [nace_r2], [na_item], [unit], [time]). Table a64 has more rows than table A10 (more values for [nace_r2] and [na_item]). I need to check the consistency of the filed [value] (available in both tables) for the rows with the same codes in the related fields in both tables. Moreover, there are blanks in both two tables. Therefore, to check the sums at different levels between the two tables, I need to exclude from the sums the related rows of both tables the rows where there is a blank in one or other table). 

I have already tried these four unsuccessful strategies:
1. to establish NON-active relationships for each related field among the two tables and apply multiple USERELATIONSHIP or CROSSFILTER.

2. Actually a10 and a64 are linked through a dictionary for each field [geo], [nace_r2], [na_item], [unit], [time].

3. Create in both tables a column of id as [time] &"_"& [geo] &"_"& [nace_r2] &"_"& [na_item] &"_"& [unit]. Linking both tables trough the id.

The latter works imperfectly: I can list the matched results but the summary metric (to analyze the deviations dynamically acording to the filters of the report is the percentual deviation of the value in a64 over a10) does not work properly because what is being reported is a sum of the percentual deviations of every row, what makes no sense).

I have created the following measures:

At table a64: 

SumT03=CALCULATE(SUM(nama_a64_BI[value]),FILTER(nama_a64_BI,nama_a64_BI[value]<>BLANK()),USERELATIONSHIP(nama_a10_BI[id],nama_a64_BI[id]))

At table a10:

SumT01 =CALCULATE(sum(nama_a10_BI[value]),FILTER(nama_a10_BI,nama_a10_BI[value]<>blank()), USERELATIONSHIP(nama_a10_BI[id],nama_a64_BI[id]))

%T3/T1 = abs(CALCULATE(divide([SumT03],SumT01])-1, USERELATIONSHIP(nama_a10_BI[id],nama_a64_BI[id])))

I am reporting in the visuals %T3/T1.

Any support would be much appreciated.

Thanks before-hand!

Best regards,

Antonio

PS: I also thought: Same as 3. but creating also a table with a single column id = [time] &"_"& [geo] &"_"& [nace_r2] &"_"& [na_item] &"_"& [unit] and linking a10 and a64 to the new table through the id. Does this makes any difference considering that a10 contains same rows as the new bridge?

[V-lianl-msft]

Hi @AFAmores ,

Based on your description,Your problem is "the summary metric does not work properly". You could try to use "SUMX" instead of "SUM" to calculate measure.It is very difficult to analyze without looking at the data and just by imagining. See if you can paste the image of the chart representing any sample data. You could see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

Best Regards,

Liang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.