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

Indicate / Filter Change in Raings

Hey Guys,

 

I've been struggling with this for a while, my data is as follows:

 

BatchItem_IDRating
115
128
132
1412
215
228
233
2412
316
328
332
3410

 

I need to be able to filter on those items that have changed values per batch, and optionally indicate if the value has gone up or down or remained the same. 

9 REPLIES 9
Super User
Super User

Re: Indicate / Filter Change in Raings

Hi @jurgensve

 

By values you mean Ratings I guess?

I quote:

...indicate if the value has gone up or down or remained the same...

What does that mean exactly? gone up or down when? An clarifying example would be useful

 

 

 

jurgensve Frequent Visitor
Frequent Visitor

Re: Indicate / Filter Change in Raings

Hi @AlB,

 

Sorry, yes if the rating from batch 1 has increased or decreased for the same object in batch 2 or three. I would need it to be dynamic as you won't always compare sequential batches.

 

For example in batch 1 ItemID 4 was 12 then in batch 3 ItemID 4 is now a 10 > indicate a decrease of 2.

Super User
Super User

Re: Indicate / Filter Change in Raings

@jurgensve

 

How are you planning to indicate which batches are to be compared? Or are you looking to check for changes over all batches together for an ItemID? If so what exactly? The difference between the max and the min ratings for that   ItemID?  

jurgensve Frequent Visitor
Frequent Visitor

Re: Indicate / Filter Change in Raings

Most of the times you will only be comparing the earliest and the latest MIN and MAX could work here. So it would normally be 2 batches.

 

I'm not sure if it will be possible to compare row by row - if you could please help to create a measure that can compare the min(batch) to the Max(batch) for the ratings, just indicate the difference if it went down it would be a negative number if it went up a positive number and no change would then be 0.

 

Appreciate your assistance. Thank you in advance.

Super User
Super User

Re: Indicate / Filter Change in Raings

@jurgensve

I'm not quite sure what you mean, but let's see if this is of any help. Create three measures as follows. First these two:

LatestBatchNumber   = MAX(Table1[Batch])

EarliestBatchNumber = MIN(Table1[Batch])

and then a third one that makes use of the two previous ones:

RatingDifference =
VAR _LatestBatchNumber = [LatestBatchNumber]
VAR _EarliestBatchNumber = [EarliestBatchNumber]
VAR LatestRating =
    CALCULATE (
        SELECTEDVALUE ( Table1[Rating] );
        Table1[Batch] = _LatestBatchNumber
    )
VAR EarliestRating =
    CALCULATE (
        SELECTEDVALUE ( Table1[Rating] );
        Table1[Batch] = _EarliestBatchNumber
    )
RETURN
    LatestRating - EarliestRating

You can then use a matrix visual in your report, with Table1[ItemID] in rows and the 3 measures in values. You will thus have, per ItemID, the number of the first batch, the number of the last batch and the difference in ratings between those.

Does that help?

Super User
Super User

Re: Indicate / Filter Change in Raings

@jurgensve

Another version for the third measure:

 

RatingDifference_V2 =
VAR LatestRating =
    LOOKUPVALUE (
        Table1[Rating];
        Table1[Batch]; [LatestBatchNumber];
        Table1[ItemID]; SELECTEDVALUE ( Table1[ItemID] )
    )
VAR EarliestRating =
    LOOKUPVALUE (
        Table1[Rating];
        Table1[Batch]; [EarliestBatchNumber];
        Table1[ItemID]; SELECTEDVALUE ( Table1[ItemID] )
    )
RETURN
    LatestRating - EarliestRating

 

 

jurgensve Frequent Visitor
Frequent Visitor

Re: Indicate / Filter Change in Raings

Hi @AlB Thank you so much, dont think I did it right:

 

RatingDifference =
VAR LatestRating =
LOOKUPVALUE(
RiskRatings[Residual_RiskRating],
RiskRatings[Batch_Key], [MaxBatch],
RiskRatings[UnitRisk_Key], SELECTEDVALUE(RiskRatings[UnitRisk_Key])
)

VAR EarliestRating =
LOOKUPVALUE(
RiskRatings[Residual_RiskRating],
RiskRatings[Batch_Key], [MinBatch],
RiskRatings[UnitRisk_Key], SELECTEDVALUE(RiskRatings[UnitRisk_Key])
)
RETURN
LatestRating - EarliestRating

Capture.PNG

 

The total column actually indicates the correct change.

Super User
Super User

Re: Indicate / Filter Change in Raings

@jurgensve

 

I ran a quick test on the example data you posted initially. Seems to work. Check it out. Get the file here

Microsoft v-jiascu-msft
Microsoft

Re: Indicate / Filter Change in Raings

Hi @jurgensve,

 

Could you please mark the proper answers as solutions?

 

Best Regards,

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
New Kudos Received Badges Coming

New Kudos Received Badges Coming

Kudos to you if you earned one of these! Check your inbox for a notification.

Microsoft Implementation for Communities Wins Award

Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Kudoed Authors (Last 30 Days)
Users online (3,665)