cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Kudo Kingpin
Kudo Kingpin

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 a 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 a 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 a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

View solution in original post

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 a 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
secondImage

Happy New Year from Power BI

This is a must watch for a message from Power BI!

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors