Forum Discussion
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 Parameters Revenue score:
Table Revenue:
Do you guys have any idea how to achieve this?
Thank you in advance,
Best regards,
Casper
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.
8 Replies
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-Forum/ba-p/963216
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/1447523Hi,
Do you want a calculated column or a measure solution? Share the download link of the PBI file.
I don't think it matters, I'm only going to use it in the Matrix visual.
Best regards,
Casper
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
Hi Ashish_Mathur and lbendlin ,
Thank you for the reply, hereby the link to the report:
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
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 Thank you so much!
