CODEWITHSUNDEEP
datatablepowerbidata-modelingmeasure
Ramez Ali
Ramez Ali

Reputation: 27

Create measure in BI which include calculation between two related tables

enter image description here

As the model in the table, I have the bill of materials table as the fact table and the price and demand tables and dimensions, I need to create 1- measure to calculate the total required quantity per item code, 2- Measure the total spendings per SKU # as per the demand table and price table which are not directly related. enter image description hereenter image description here

I was trying to use related functions however it was not matching the data which should claculated by the measure

Upvotes: 0

Views: 50

Answers (1)

RADO
RADO

Reputation: 8148

To make DAX code easy and fast, you need to re-design your data model. You current model has several mistakes:

  • "Demand" is not a dimension. It must be a fact table;
  • Demand quantity is at SKU level, while BOM is at item level - their levels of detail mismatch;
  • BOM fact tables contains several fields such as Item Description which are clearly dimensional attributes, they belong to the Item dimension;
  • "Price", on the other hand, does not belong to the dimension, it should be in the fact table.

As a general rule, if a field is used for filtering reports (for example, as a slicer), then it belongs to a dimension. If a field is used as an input in calculations, it belongs to a fact table.

Here is how I would change your data model:

enter image description here

  • I would move item description and units of measure to "Item" dimension;
  • Move "Price" to the fact table BOM;
  • Change "Demand" into a dimension "SKU", and move demand Qty field to BOM (as SKU Qty);

Then DAX measures become very simple.

For total required Qty of Items:

Required Item Qty = SUM ( BOM[Item Qty])

For the measure you asked (total required cost of items):

Required Item Cost =SUMX( BOM, BOM[Item Qty] * BOM[Price] * BOM[SKU Qty])

And your report might look like this:

enter image description here

with the ability to drill down:

enter image description here

The only thing you need to be careful is demand. Since moving SKU Qty to BOM duplicates numbers, you can't simply sum them up to show total demand. You need to de-duplicate numbers first, for example:

Total Demand = 
SUMX ( 
   SUMMARIZE ( BOM, BOM[SKU], BOM[SKU Qty] ), 
   BOM[SKU Qty] 
)

which gives you this report:

enter image description here

Upvotes: 1

Related Questions