Finding percentage of number using columns from different tables.

[Gary_RH]

Hi,

Very new user here so apologies in advance, I've done several searches but I'm struggling. Maybe it's my terminology, any help would be appreciated.

I have a tables that have a relationship by a reference number, one table has a column that shows the starting value (which varies for each reference) which I would like to compare another column against which is the final value of that original number.

How would I get a gauge, for example, to display the remaining % of the initial starting value for each reference if I was to select a reference in another visual, like a slicer or bar chart for example.

For context, the starting value is the thickness of a piece of steel, the final value is the thickness of the steel following corrosion of that steel. Each starting value varies slightly.

Table 1 with starting values

Table 2 with final values

Any help would be greatly appreciated, thank you in advance.

Gary

[bcdobbs · Accepted solution]

Hi,

I've been thinking about best way to model this. Various ways to go but I think I'd do the following...

1) Create a relationship between the location id's. Table 1 becomes a location dimension and table 2 is a measurements fact table. There should be a 1 to many relationship between them.

2) I then think the easiest thing to do is create a "Latest measurement" calculated column on table 1 with the following DAX:

Latest Measurement = 
	CALCULATE ( 
		MIN 'Table 2'[Measured-Thickness] 
		)

The CALCULATE is needed as it forces "context transition", it moves the current row into the filter context so you only get the related minimum thickness.
Once you have that you can then use a measure like you've already written. (Normally you want to minimise the use of calculated columns but this seems to be a reasonable option here).

[Gary_RH]

Well....I think I'm close.

It works well where there has only be 1 test per location as shown

However, where there is more than one test per location it seems to summerise the %age value (as shown), how would I go about just making sure it only shows the lowest value?

Thank you 🙂

[Gary_RH]

I think I've solved it...?

%age difference = DIVIDE(SUM('Table-2'[Measured-Thickness]), SUM('Table-1'[Specified-Thickness (mm)]))

then format as percentage..

[bcdobbs]

That would certainly give you a percentage! I was about to ask exactly what sort of final output you wanted. Eg an overall value or something more specific to a particular location displayed in a matrix.

Only issue i can see with the measure you've written is that if you used it in a visual not filtered to a specific day it would be taking all the repeated measurements into account. If you can share what output you need I'll happily write something tomorrow for you. (Equally though what you have might be fine!)

[Gary_RH]

Thanks for the reply. I'm basically after a card to display the remaning %age of steel remaining as shown in the image below (my later post). I got it part working by replacing the DIVIDE(SUM..... to DIVIDE(MIN.... and that seems now to dispaly the MIN value for a particular location, so if there were 2 readings it would show the MIN value, not the latest. 

One problem I'm having is when nothing is seleceted both the gauge and the card seem to show a random value. 

I'm currently doing my best to learn Power BI, but I've come to realise the magic is in the DAX...which is my next challange once I've mastered the basics of the software.

[bcdobbs]

In Table 2 do you have a date column as well? You seem to have multiple measurements per location?

[Gary_RH]

Hi @bcdobbs, yes I do, tests taken in the same location on different dates