Forum Discussion

AFra's avatar
AFra
Helper III
6 years ago
Solved

Group values based on another table

Hi all, 

 

I have a table with a list of salaries (Table A.), and another table with the categories I'd like to add (Table B.) 

Table A. 

IdpersonSalary
5792714 669,47 €
6358410 616,88 €
6358120 584,60 €
4846010 712,40 €
5654210 712,38 €
4846015 550,85 €
8060913 297,22 €
8060911 617,99 €
4351716 938,55 €
4351722 256,46 €
454506 477,12 €
4351713 533,18 €
4351712 139,92 €
4351715 365,90 €
9872210 408,80 €
9872210 712,40 €
9676816 263,52 €
9845113 993,00 €
10103710 617,36 €
9931813 261,92 €
9677116 720,08 €
9677114 348,54 €
9677115 082,08 €

Table B. 

Rev ménage (groups)MinValMaxValORDRE
< 5.000 €-10004999,99991
5.000 - 10.000 €50009999,99992
10.000 - 12.500 €1000012499,99993
12.500 - 15.000 €1250014990,99994
15.000 - 20.000 €1500019999,99995
20.000 - 25.000 €2000024999,99996
25.000 - 30.000 €2500029999,99997
30.000 - 40.000 €3000039999,99998
40.000 - 50.000 €4000049999,99999
> 50.000 €5000010000000010
(Blank)  11

 

So that I can have : 

IdpersonSalaryRev ménage (groups)
5792714 669,47 €12.500 - 15.000 €
6358410 616,88 €10.000 - 12.500 €
6358120 584,60 €20.000 - 25.000 €
4846010 712,40 €10.000 - 12.500 €
5654210 712,38 €10.000 - 12.500 €

 

How can I tell that power bi ? It's a sort of lookupvalue but that takes in account the max and min value. 

Any ideas? 

Thanks in advance! 

 

Ana 

Need Help

3 Replies

    • AFra's avatar
      AFra
      Helper III
      6 years ago

      Thanks Mariusz , it works nicely : ) 

       

      Car revenus ménage = CALCULATE(
                      VALUES('cat revenus'[Rev ménage (groups)]) ;
                      FILTER('cat revenus';
                                  data[Rev ménage]>='cat revenus'[MinVal]
                                  && data[Rev ménage] < 'cat revenus'[MaxVal]
                       )
      )
      • Mariusz's avatar
        Mariusz
        Community Champion
        6 years ago

        Hi AFra 

         

        Happy to help!

         

         

        Best Regards,
        Mariusz

        Please feel free to connect with me.
        Mariusz Repczynski