Dashboard malfunction with realtionships

[agcbridgestone]

I trying to prepare a dashboard with an excel sheet as datasource the excel have 2 tables with the following relations between then:

One to many relation between SPEC (Codigo-Version) and LOTES(Codigo-Version)

SPEC DATA

Codigo-Version	Peso_metro
R-245-132.1	15,02
R-430-21.1	1,47
R-245-150.0	15,24
R-320-143.0	9,8

LOTES_DATA

ID_LOTE	FECHA_HORA	VERSION_NUMBER	CODIGO	STATUS	ID_MAQUINA	TIEMPO_ESPERA	Codigo-Version
20170316002712R-284	16/03/2017 0:27:12	67.0	R-284	0	10x10	148	R-284-67.0
20170315235552R-320	15/03/2017 23:55:52	143.0	R-320	1	10x10	137	R-320-143.0
20170315014015R-0426	15/03/2017 1:40:15	82.0	R-0426	1	10x10	144	R-0426-82.0
20170315232140R-245	15/03/2017 23:21:40	150.0	R-245	1	10x10	52	R-245-150.0
20170315120636R-245	15/03/2017 12:06:36	150.0	R-245	0	10x10	138	R-245-150.0

When I put the information in a dashboard  I find the following problem:

In the first grid the info it's displayed correctly the relations defined are correctly aplyed.

But in the second grid the info it's not ok because it's calculating the average of all the records of SPEC DATA with out taking in consideration the relations that are defined in the model.

[v-haibl-msft]

@agcbridgestone

How do you want the table visual show? If you want the first and last row to show blank and other rows show 10.38. You can try to create a calculated column in LOTES table with following formula.

Column =
IF ( RELATED ( SPEC[ Peso_metro] ) <> BLANK (), AVERAGE ( SPEC[ Peso_metro] ) )

Best Regards,

Herbert

[agcbridgestone]

Hi,

The problem that I have detect it's that the avergage value that the dashboard it's calculating is the average of the whole datatable values without taking into account the relationships between tables. For example:

For this ID_LOTE:20170315232140R-245 in LOTES_TABLE which Codigo-Version R-245-150.0 (the filed which relate both tables)

The unique value related of peso metro in Spec table is 15,24 for the Codigo-Version R-245-150.0 

So I really don't understand why it's displaying the average of all peso metro values in spec table, which is 10,38.

Thnks for your support

[v-haibl-msft]

@agcbridgestone

The average measure here will not consider that if the related value in another table is blank, it will do the average calculation for all values. If you want to only show the average for those values whose related value is not blank, you need to create a measure with some conditions.

Best Regards,
Herbert