Forum Discussion
measure with data from 2 tables
I want to create a measure that uses a measure in table one divided by a value in a summarized table.
I can show both values in a table vizualization but I can not get the measure to work.
2 Tables:
1. Table 'demand' (via DAX SUMMARIZE per item the demand from another table)
| item | demand |
| A | 100 |
| B | 330 |
| C | 60 |
2. Table with Purchase Orders 'POs'
| Ponr | item | PoQty |
| 1 | A | 50 |
| 2 | B | 100 |
| 3 | B | 100 |
| 4 | C | 20 |
| 5 | A | 10 |
| 6 | C | 10 |
In that Po's table I have a maesure: 'TotalPOQty' is a SUM of the POQty filtered on a date range
The result of this measure is:
| item | POQty |
| A | 60 |
| B | 200 |
| C | 30 |
the Pos table and the demand table are related to each other through a distinct-item table
What I require as a result is this:
| item | PoQty | demand | %demand |
| A | 60 | 100 | 60% |
| B | 200 | 330 | 61% |
| C | 30 | 60 | 50% |
Measure '%demand' should be the PoQty divided by the demand
and I would like to have this measure in the TABLE POs
How do I create that measure?
Thanks!
MMJ369 Please try using "New Table" option
DemandQuantityResult = ADDCOLUMNS(Demand ,"POQty",LOOKUPVALUE(Quantity[POQty],Quantity[item],Demand[item]) ,"% Demand",ROUND((LOOKUPVALUE(Quantity[POQty],Quantity[item],Demand[item])/Demand[demand])*100,0))
4 Replies
- PattemManohar
Community Champion
MMJ369 Please try using "New Table" option
DemandQuantityResult = ADDCOLUMNS(Demand ,"POQty",LOOKUPVALUE(Quantity[POQty],Quantity[item],Demand[item]) ,"% Demand",ROUND((LOOKUPVALUE(Quantity[POQty],Quantity[item],Demand[item])/Demand[demand])*100,0))Thanks for the suggestion.
I do not have a table with the quantities. The PO quantities are only held in a measure within the PO-table.
Should I first create a table with the first two columns and then use your dax to add the rest?
I really need to be able to use the demand per item in the PO table.
- PattemManohar
MMJ369 Please create an intemediate "Quantity" table by using your "POs" table, grouping on Item and suming up the quantity
vik0810Resolver V
What does your data model look like? It should work, if you use a table visualization, put an item from your items table in it, and then following measures:
SUM(POs[PoQty])
SUM(Table[Demand])
DIVIDE( SUM(POs[PoQty]), SUM(Table[Demand]) )
