cancel
Showing results for
Did you mean:
Highlighted
Frequent Visitor

## Fact Table With Multiple Units of Measure

Hello Everyone!

I'm just starting to learn data modeling and DAX and I'm going crazy with a measure I want to achieve. I have the Data Model in the screenshot. Where:

• OrdreCompra-Detall (F4311) is a purchase order line item fact table. Where I have these columns for the calculation:
• ArticlePlanta (F4102) is my Dimension Product.
• Article (F4101) is a subset of atributes for Dim Product. Where I have the different Units of Measure of the product.
• FactorsConversió (F41002) where the conversion factors for the different units of the product are stored.

Data Model

Every Product has a primary UoM which can be units, kg or liters. My goal is to calculate the quantity received in KG for every line as a measure so we can analyze purchases by KG or LITERS not only in currency format.

For the measure I guess I'll have to use an Iterator, for example SUMX, over the fact table. See my starting point below:

``````Quantity Received (KG) =
SUMX(
OrdreCompra-Detall (F4311);
'OrdreCompra-Detall (F4311)'[Quantity Received] * CONVERSION FACTOR
)``````

I have no clue on how to start writing the DAX code to get the CONVERSION FACTOR in my current situation. How can I get the data from the table with the conversion factors in this situation? Is there anything you think I could do to improve the Data Model?

Thank everyone for the help! All inputs appreciated!

2 REPLIES 2
Highlighted
Super User II

## Re: Fact Table With Multiple Units of Measure

Can you add sample tables (in format that can be copied to PowerBI) from your model with anonymised data? Like this (just copy and paste into the post window).

 Column1 Column2 A 1 B 2.5
Thank you for the kudos 🙂

Proud to be a Datanaut!

Highlighted
Frequent Visitor

## Re: Fact Table With Multiple Units of Measure

@Stachu I've filtered the data for one of the articles which has multiple units of measure. Not all articles have them. Hope this helps!

OrdreCompra-Detall (F4311)

Spoiler
 _Document _CodiLinia _Adreça _Data Entrega Sol·licitada _Data Creació PO _Data Entrega Promesa _Data Entrega _Item _Article Quantitat Demanada UoM Quantitat Demanada Quantitat Rebuda Cost Unitari Quantitat Demanada Article Pest Total Línia Pes Total Línia UoM Volum Total Línia Volum Total Línia UoM _UnitatNegoci _ArticlePlanta_FK Quantitat Demanada Article UoM 20000033 3000 16184 120023 120007 120023 120023 5087 101098 UN 14 14 187 350 350 KG 14 LT 10VILFR1 10VILFR1:5087 KG 20000838 3000 16184 120085 120071 120085 120084 5087 101098 UN 162 162 187 4050 4050 KG 162 LT 10VILFR1 10VILFR1:5087 KG 20000218 1000 16184 120037 120024 120037 5087 101098 UN 44 0 187 1100 1100 KG 44 LT 10VILFR1 10VILFR1:5087 KG 20001028 1000 16184 120118 120083 120118 0 5087 101098 UN 74 0 187 1850 1850 KG 74 LT 10VILFR1 10VILFR1:5087 KG 20000280 1000 16184 120037 120029 120037 120036 5087 101098 UN 44 44 187 1100 1100 KG 44 LT 10VILFR1 10VILFR1:5087 KG 20000461 1000 16184 120054 120043 120054 120051 5087 101098 UN 25 25 187 625 625 KG 25 LT 10VILFR1 10VILFR1:5087 KG 20000496 1000 16184 120055 120045 120055 120055 5087 101098 UN 28 28 187 700 700 KG 28 LT 10VILFR1 10VILFR1:5087 KG 20000336 1000 16184 120050 120034 120050 120049 5087 101098 UN 37 37 187 925 925 KG 37 LT 10VILFR1 10VILFR1:5087 KG 20000587 1000 16184 120062 120051 120062 120059 5087 101098 UN 28 28 187 700 700 KG 28 LT 10VILFR1 10VILFR1:5087 KG 20000655 1000 16184 120065 120058 120065 120064 5087 101098 UN 56 56 187 1400 1400 KG 56 LT 10VILFR1 10VILFR1:5087 KG 20000760 1000 16184 120078 120065 120078 120077 5087 101098 UN 25 25 187 625 625 KG 25 LT 10VILFR1 10VILFR1:5087 KG

ArticlePlanta (F4102)

Spoiler
 _Item _UnitatNegoci _Marca _Eco/Conv _Tipología _Subclassificació _Alérgenos _Intrastat Branch/Plant Eco/Convencional Tipología Subclasificación Alérgeno _ArticlePlanta_PK 5087 10VILFR1 CON MP IBR FRIGORIFIC CONVENCIONAL MATERIA PRIMERA INGREDIENT . 10VILFR1:5087

Article (F4101)

Spoiler
 _Item _Article _Marca _Marca/Propiedad UoM Primary UoM Secondary UoM Purchasing UoM Weight UoM Volume Descripció Article 5087 101098 KG UN UN KG LT GLUCOZYME BIDÓ 25 KG

FactorsConversió (F41002)

Spoiler
 _Item _FromUoM _ToUoM Conversion Factor To UoM From UoM 5087 KG LT 0,04 Liters Kilograms 5087 KG UN 0,04 Units Kilograms

Announcements

#### August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

#### July 2020 Community Highlights

Learn about the exciting things that happened in July.

#### Get Ready for Power BI Dev Camp

We are thrilled to announce we will begin running a monthly webinar series named Power BI Dev Camp.