I’m currently building a dashboard that consist of data sources with multiple level of relationship. Some of the tables do not communicate with each other and do not have a relationship that I can combine.
Table Relationship
What I’m trying to achieve is to combine all 3 measures into 1 table. Ideally, the intended calculated measure I’m looking for is to get something like this.
Overall Yield = Yield A (From Table A) * Yield B (From Table B) * Yield C (From Table C).
I tried to create a relationship across the 3 tables but as they don’t talk to each other, the results being shown over is not correct and is showing one single value across day, week & month. I also tried to play around by linking the 3 tables into a master calendar table and putting the combined measure there, but it’s not working as well. My thought process here is to combine the 3 measures into 1 and store this combined measure in a calendar table so that I can use the Date from Calendar table as the main Date column. However, I’m not really sure how I can navigate this, and this is where I’m stuck at.
The expected output I’m expecting to retrieve from my data source is something like this.
Output
rvnd is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
3
pls modify your measures
Yield A Formula =
CALCULATE (
SUM ( 'Yield A'[FINISHQTY] ),
FILTER (
'Yield A',
'Yield A'[Week Number] = SELECTEDVALUE ( 'Calendar'[Week Number] )
)
)
/ CALCULATE (
SUM ( 'Yield A'[STARTQTY] ),
FILTER (
'Yield A',
'Yield A'[Week Number] = SELECTEDVALUE ( 'Calendar'[Week Number] )
)
)
Yield B Formula =
CALCULATE (
SUM ( 'Yield B'[FINISHQTY] ),
FILTER (
'Yield B',
'Yield B'[Week Number] = SELECTEDVALUE ( 'Calendar'[Week Number] )
)
)
/ CALCULATE (
SUM ( 'Yield B'[STARTQTY] ),
FILTER (
'Yield B',
'Yield B'[Week Number] = SELECTEDVALUE ( 'Calendar'[Week Number] )
)
)
NEW START QTY =
CALCULATE (
SUM ( 'Yield C'[NEW FINISH QTY] ),
FILTER (
'Yield C',
'Yield C'[Week Number] = SELECTEDVALUE ( 'Calendar'[Week Number] )
)
)
+ CALCULATE (
SUM ( 'Yield C'[BNU Qty] ),
FILTER (
'Yield C',
'Yield C'[Week Number] = SELECTEDVALUE ( 'Calendar'[Week Number] )
)
)
Yield C Formula =
CALCULATE (
SUM ( 'Yield C'[NEW FINISH QTY] ),
FILTER (
'Yield C',
'Yield C'[Week Number] = SELECTEDVALUE ( 'Calendar'[Week Number] )
)
) / [NEW START QTY]
then create a new measure
MEASURE =
VAR _a =
IF ( ISBLANK ( 'Yield A'[Yield A Formula] ), 1, 'Yield A'[Yield A Formula] )
VAR _b =
IF ( ISBLANK ( 'Yield B'[Yield B Formula] ), 1, 'Yield B'[Yield B Formula] )
VAR _c =
IF ( ISBLANK ( 'Yield C'[Yield C Formula] ), 1, 'Yield C'[Yield C Formula] )
RETURN
_a * _b * _c