Using a measure to calculate %

[ethanlsaul]

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.  

Product	Week1
Markers	10

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

Product	Week1
Markers	3

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

[Anonymous · Accepted solution]

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

[lbendlin]

Change your table structure

Product	Week	Requested	Received
Markers	1	10	3

Then the percentage calculation becomes easy.

[ethanlsaul]

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]

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.

[ethanlsaul]

I tried to merge, but for some reason, after i merged, it had the columns from table 1 and the table 2 was a single column that it wanted me to drill down. Any tips on how to do it correctly?

[lbendlin]

You are supposed to expand that new column, not drill down into any of the values.  Use the icon in the column header.