Forum Discussion

ethanlsaul's avatar
ethanlsaul
Icon for Helper I rankHelper I
4 years ago
Solved

Using a measure to calculate %

I have 2 tables. 

 

Table1 is what the qty i asked for. Example: I asked my supply chain partners for 10 markers for next week.  

 

ProductWeek1
Markers10

 

Table2 is what I recived the following week (week1). Example: I recieved 3 markers.

 

ProductWeek1
Markers3

 

I want to create a measure that says I recieved 30% of what i asked for. 

Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi  ethanlsaul ,

    Here are the steps you can follow：

    1. Create measure.

    Measure =
var _table1=CALCULATE(SUM('Table1'[quantity]),FILTER(ALL('Table1'),'Table1'[Product]=MAX('Table1'[Product])&&'Table1'[Week]=1))
var _table2=CALCULATE(SUM('Table2'[Recieved]),FILTER(ALL(Table2),'Table2'[Product]=MAX('Table2'[Product])))
return
DIVIDE(
    _table2,_table1)

    2. Result:

     

    Best Regards,

    Liu Yang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

6 Replies

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    4 years ago

    Change your table structure

     

    Product Week Requested Received
    Markers 1 10 3

     

    Then the percentage calculation becomes easy.

    • ethanlsaul's avatar
      ethanlsaul
      Icon for Helper I rankHelper I
      4 years ago

      Hi Ibedlin,

       

      Hi There,

       

      I just realized my inital post's table got messed up. 

       

        

      Column 1 = Product

      Column 2 = Requested #

      Product || Requested #

      Markets || 10

       

      Table 2 

      Product || Recieved

       

       

      Assume i cannot change the way the raw data is given as they 2 tables come from different source data. Is a measure impossible?

      • lbendlin's avatar
        lbendlin
        Icon for Super User rankSuper User
        4 years ago

        The best approach is to merge your tables in Power Query to arrive at the format I mentioned.

         

        You can create measures with the tables being separate but that would be contrary to the Power BI design ideas, and would result in a fragile solution.

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi  ethanlsaul ,

    Here are the steps you can follow：

    1. Create measure.

    Measure =
var _table1=CALCULATE(SUM('Table1'[quantity]),FILTER(ALL('Table1'),'Table1'[Product]=MAX('Table1'[Product])&&'Table1'[Week]=1))
var _table2=CALCULATE(SUM('Table2'[Recieved]),FILTER(ALL(Table2),'Table2'[Product]=MAX('Table2'[Product])))
return
DIVIDE(
    _table2,_table1)

    2. Result:

     

    Best Regards,

    Liu Yang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly