Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Paulyeo11
Impactful Individual
Impactful Individual

How to divide from 2 measure

Hi All

 

I have 2 measure  , may i know how to create one more measure below :-

Ratio = AMT_OH / AMT_AVE

 

1) measure

AMT_OH = SUM(INVC[Unit Cost])*SUM(INVC[Avail])
 
2) measure
AMT_AVE =
((
SUM(INVC[S_1])+
SUM(INVC[S_2])+
SUM(INVC[S_3])+
SUM(INVC[S_4])+
SUM(INVC[S_5])+
SUM(INVC[S_6])+
SUM(INVC[S_7])+
SUM(INVC[S_8])+
SUM(INVC[S_9])+
SUM(INVC[S_10])+
SUM(INVC[S_11])+
SUM(INVC[S_12])
)
/12)*
SUM(INVC[Unit Cost]
)
 
Paul
 
1 ACCEPTED SOLUTION

Ratio = DIVIDE([AMT_OH],  [AMT_AVE])

 

it looks like your fact table structure is not idea. You should consider unpivoting the columns in your INVC table. This is quite a big topic that we cover in our Power BI dimensional modelling course at Skillwave.Training, but you can no doubt find other material on the web too. 



* Matt is an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

View solution in original post

5 REPLIES 5

Because I have 6 year's experience in data modelling 😀

Your measure AMT_AVE requires yo to add data from 12 columns. If you unpivoted the data you would only have to add from a single column. The other 2 columns may be best unpivoted too, or in another table - I can't say without looking 



* Matt is an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

Ratio = DIVIDE([AMT_OH],  [AMT_AVE])

 

it looks like your fact table structure is not idea. You should consider unpivoting the columns in your INVC table. This is quite a big topic that we cover in our Power BI dimensional modelling course at Skillwave.Training, but you can no doubt find other material on the web too. 



* Matt is an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

Hi Matt

 

Below is my flat table :- 

https://www.dropbox.com/s/s4rum78l5kzahlm/INVC_TS.csv?dl=0

Do you mean this table can be further improve ?

 

By the way i have AR Table below :-

https://www.dropbox.com/s/ll87ldkmwnafuk1/AR_TS.csv?dl=0

Can you give me some comment ?

 

Paul Yeo

It is a very big topic.  The first file is a single flat file, and it is very small.  It is questionable if you would want to/need to invest in building a dimensional model in this case.  Sometimes the effort is not worth the benefit.  You have already written the measure combining the total of 12 columns, so you are not going to gain much now.  Having said that, a better dimensional model would have a fact table with a single column for the S_ column values and an attribute column describing what the value refers to. What that means for the other columns is hard to say - it depends on what the data are. 



* Matt is an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

Hi Matt

Thank you for the expression it work,

May i know how you know my data model structure is no good ?

Paul

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.