Find Value in Table related to Row Context

[paulalmond91]

Hi,

I have a Matrix table that displays two tiers of row context followed by a calculated measure.

In essence, we need to display multiple rows with independent targets found in a separate table against the calculated values.

Here is a screenshot of one of the table examples:

Alongside the % I'd like to add a target value. Spreadsheet attached below:

I need to create a link between each row as they all have individual targets.

What would be the best way to set this up?

[v-lili6-msft]

hi, @paulalmond91

You may try these two ways:

1. Create tooltips based on report pages in Power BI Desktop.

https://docs.microsoft.com/en-us/power-bi/desktop-tooltips

2. Use Drillthrough in Power BI Desktop.

https://docs.microsoft.com/en-us/power-bi/desktop-drillthrough

Best Regards,

Lin

[paulalmond91]

Thanks for the tips.

How does this determine the correct target percentage to apply though?

For example If I were to select no on foundation apprenticeship from my above visuals, then this figure is different from No on Apprenticeship.

Thanks

Paul

[v-lili6-msft]

hi, @paulalmond91

You could drag two type field into drill- through 

for example:

Then it could determine the correct target.

By the way, what is the relationship between basic table and target table, they need a relationship among them for these feathers.

and I could find the relationship by your two screenshots.

If not your case, please share your sample data and expected output. 

Best Regards,

Lin

[paulalmond91]

Thanks for the further help.

I haven't got a relationship linked as I have no correlating fields between them (or in this case too many).

You can find a sample file with this here: https://www.dropbox.com/s/dv16915d3nmlacx/sample.pbix?dl=0

I hope I've explained my goal but just to summarise on the LOR Targets table there is a target for Yes Disability and No Disability (which are different) and then they are different again for each row category beneath the yes/no which is higher apprenticeship, apprenticeship and foundation apprenticeship.

Below is a screenshot of what I'm trying to achieve within PowerBI

[v-lili6-msft]

hi, @paulalmond91

After my research, your first level YES/NO can't be linked to Overall, for it is calculated manually but in the martix it will

calculate by dax.

You may try this way:

For these two tables should be linked by Disability and Programme Type in Learner table with Category and Attribute in LOR Targets table, we could combine two fields for each table and then use them to create a relationship for two table.

Learner : relation = Learner[Disability]&Learner[Programme Type]

LOR Targets : relation = 'LOR Targets'[Category]&'LOR Targets'[Attribute]

Then create the relationship by it

Then create two measure

Target % = CALCULATE(SUM('LOR Targets'[Target]))

Variance = [% Achievement]-[Target %] 

Result:

Best Regards,

Lin

[paulalmond91]

HI Lin,

Thanks for your effort on this - think getting closer to a solution.

2 things:

1. To confirm there is no way to have a single measure for the target value for yes/no (aka first row) and for then the second row targets
2. If I wanted to create targets for things such as Age + Programme type - would I need to create a new table to make a new relationship and use that table for the custom fields or is there a way I could have multiple criteria's on the same table?

Thanks

Paul

[v-lili6-msft]

hi, @paulalmond91

1. I'm afraid it couldn't achieve for now, target value for yes/no is based on its child level values.

2. You could create multiple relationships other different tables, there are also multiple relationships can be created between two tables, but there is only one active relationship, If you want to use these inactive relationships to do a calculation, you could use

USERELATIONSHIP Function in your formula.

Best Regards,

Lin