Revenue score between two parameters from another table

[CasperSV]

Hi guys,

I am trying to add a score (from parameters in another table) based on the expected year revenue.

Current result in Matrix:

Expected result:

Dataset,

Table Parameters Revenue score:

Table Revenue:

Do you guys have any idea how to achieve this?

Thank you in advance,

Best regards,

Casper

[CasperSV]

Hi @Ashish_Mathur and @lbendlin ,

Thank you for the reply, hereby the link to the report:

https://smartval-my.sharepoint.com/:u:/g/personal/casper_smart-value_nl/ER4QZVL-ufVBgRQGzkQXo4wBX0s1...

Expected result:

Customer	Item category	Expected year revenue	Score
Alex	Total	2.342,86
	Chair	457,14	A
	Plate	685,71	Z
	Spoon	628,57	A
	Table	571,43	Z
Mike	Total	3.085,71
	Chair	800,00	A
	Plate	857,14	Z
	Spoon	514,29	Z
	Table	914,29	A

Thank you for the help.

Best regards,

Casper

[Ashish_Mathur · Accepted solution]

Hi,

Write this calculated column formula in the Revenue table

Score = CALCULATE(MAX('Score parameters'[Score]),FILTER('Score parameters','Score parameters'[Item category]=EARLIER(Revenue[Item category])&&'Score parameters'[Revenue min]<=Revenue[Expected year revenue]&&'Score parameters'[Revenue max]>=Revenue[Expected year revenue]))

Write this measure and drag it to the visual

S = MAX(Revenue[Score])

Hope this helps.

[CasperSV]

@Ashish_Mathur Thank you so much!

[Ashish_Mathur]

You are welcome.

[Ashish_Mathur]

Hi,

Do you want a calculated column or a measure solution?  Share the download link of the PBI file.

[CasperSV]

Hi @Ashish_Mathur 

I don't think it matters, I'm only going to use it in the Matrix visual. 

Best regards,

Casper

[gmsamborn]

Hi @CasperSV 

Would this work as a measure?

Score = 
VAR _Cat = SELECTEDVALUE( 'Revenue'[Item category] )
VAR _Rev = SELECTEDVALUE( 'Revenue'[Expected year revenue] )
VAR _Score =
    CALCULATE(
        MAX( 'Score parameters'[Score] ),
        FILTER(
            'Score parameters',
            'Score parameters'[Item category] = _Cat
                && 'Score parameters'[Revenue min] <= _Rev
                && 'Score parameters'[Revenue max] >= _Rev
        )
    )
RETURN
    _Score

[lbendlin]

Please provide sample data (with sensitive information removed) that covers your issue or question completely, in a usable format (not as a screenshot). Leave out anything not related to the issue.
If you are unsure how to do that please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Please show the expected outcome based on the sample data you provided.

If you want to get answers faster please refer to https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...