cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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:
    • Quantitat Rebuda (Quantity Received)
    • Quantitat Demanada UoM (Quantity Received Unit of Measure)
  • 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 ModelData 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
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
Did I answer your question? Mark my post as a solution!
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_ArticleQuantitat Demanada UoMQuantitat DemanadaQuantitat RebudaCost UnitariQuantitat Demanada ArticlePest Total LíniaPes Total Línia UoMVolum Total LíniaVolum Total Línia UoM_UnitatNegoci_ArticlePlanta_FKQuantitat Demanada Article UoM
200000333000161841200231200071200231200235087101098UN1414187350350KG14LT10VILFR110VILFR1:5087KG
200008383000161841200851200711200851200845087101098UN16216218740504050KG162LT10VILFR110VILFR1:5087KG
20000218100016184120037120024120037 5087101098UN44018711001100KG44LT10VILFR110VILFR1:5087KG
2000102810001618412011812008312011805087101098UN74018718501850KG74LT10VILFR110VILFR1:5087KG
200002801000161841200371200291200371200365087101098UN444418711001100KG44LT10VILFR110VILFR1:5087KG
200004611000161841200541200431200541200515087101098UN2525187625625KG25LT10VILFR110VILFR1:5087KG
200004961000161841200551200451200551200555087101098UN2828187700700KG28LT10VILFR110VILFR1:5087KG
200003361000161841200501200341200501200495087101098UN3737187925925KG37LT10VILFR110VILFR1:5087KG
200005871000161841200621200511200621200595087101098UN2828187700700KG28LT10VILFR110VILFR1:5087KG
200006551000161841200651200581200651200645087101098UN565618714001400KG56LT10VILFR110VILFR1:5087KG
200007601000161841200781200651200781200775087101098UN2525187625625KG25LT10VILFR110VILFR1:5087KG

ArticlePlanta (F4102)

Spoiler
_Item_UnitatNegoci_Marca_Eco/Conv_Tipología_Subclassificació_Alérgenos_IntrastatBranch/PlantEco/ConvencionalTipologíaSubclasificaciónAlérgeno_ArticlePlanta_PK
508710VILFR1 CONMPIBR  FRIGORIFICCONVENCIONALMATERIA PRIMERAINGREDIENT.10VILFR1:5087

Article (F4101)

Spoiler
_Item_Article_Marca_Marca/PropiedadUoM PrimaryUoM SecondaryUoM PurchasingUoM WeightUoM VolumeDescripció Article
5087101098  KGUNUNKGLTGLUCOZYME BIDÓ 25 KG

 FactorsConversió (F41002)

Spoiler
_Item_FromUoM_ToUoMConversion FactorTo UoMFrom UoM
5087KGLT0,04LitersKilograms
5087KGUN0,04UnitsKilograms

 

Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

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

July 2020 Community Highlights

Learn about the exciting things that happened in July.

Get Ready for Power BI Dev Camp

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.

Power BI Desktop August 2020 Update

Power BI Desktop August 2020 Update

We have great updates this month! Click the link for the video with more info.