Forum Discussion

CasperSV's avatar
CasperSV
Helper II
2 years ago
Solved

Revenue score between two parameters from another table

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 Param...
Need Help
  • Ashish_Mathur's avatar
    Ashish_Mathur
    2 years ago

    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.

     

Ashish_Mathur's avatar
Ashish_Mathur
Super User
2 years ago

Hi,

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

CasperSV's avatar
CasperSV
Helper II
2 years ago

Hi Ashish_Mathur 

 

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

 

Best regards,

Casper

  • gmsamborn's avatar
    gmsamborn
    Super User
    2 years ago

    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