Forum Discussion

RubySmith's avatar
RubySmith
Frequent Visitor
3 years ago
Solved

Is there a way to get / reference a specific table field / value in a measure

Hi,    New to PowerBi, I have a Main Table with User data, as well as Current License. In a Supplemenatry table, I have a list of all Licenses and associated Annual Cost.    Main Table Compan...
  • Uzi2019's avatar
    3 years ago

    Hi RubySmith 
    I created the same scenario. I am getting the result that you want. please do the following steps.


    Step1-) Create 2 seperate table like in your case 

     

     steps2 ) create relationship between 2 tables based on license.

     

    Add cost to the Main table:

     

     

    step3) create measure under Main table for user count.

    Count(user) = COUNT('Main (1)'[User])

     

    Step 4) create the following measure  (It finds Min value in annual cost and multiple with user count like(2*10)

     
    Min license =
    var M= CALCULATE(MIN('Main(cost)'[Annual Cost]),ALLSELECTED('Main (1)'[Company]))
    var N= [Count(user)]*M
    Return N
     

    step5) create new measure for Annua cost saving

     

    Annual cost saving = sum('Main(cost)'[Annual Cost])- [Min license]

     



    I hope I answered your question please give kudos and accept it as a solution!
    Thanks