cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

Dashboard malfunction with realtionships

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)One to many relation between SPEC (Codigo-Version) and LOTES(Codigo-Version)

 

SPEC DATA

 

Codigo-VersionPeso_metro
R-245-132.115,02
R-430-21.11,47
R-245-150.015,24
R-320-143.09,8

 

LOTES_DATA

 

ID_LOTEFECHA_HORAVERSION_NUMBERCODIGOSTATUSID_MAQUINATIEMPO_ESPERACodigo-Version
20170316002712R-28416/03/2017 0:27:1267.0R-284010x10148R-284-67.0
20170315235552R-32015/03/2017 23:55:52143.0R-320110x10137R-320-143.0
20170315014015R-042615/03/2017 1:40:1582.0R-0426110x10144R-0426-82.0
20170315232140R-24515/03/2017 23:21:40150.0R-245110x1052R-245-150.0
20170315120636R-24515/03/2017 12:06:36150.0R-245010x10138R-245-150.0

 

 

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

 

Dashboard1.jpg

 

 

 

 

 

 

 

 

 

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.

 

 

 

 

 

 

 

 

 

3 REPLIES 3
Highlighted
Microsoft
Microsoft

@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] ) )

Dashboard malfunction with realtionships_1.jpg

 

Best Regards,

Herbert

Highlighted

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

 

 

Highlighted

@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

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors