cancel
Showing results for
Did you mean:
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.
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.
Kudo Kingpin

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.
Kudo Kingpin

Hi Matt

Thank you for the expression it work,

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

Paul

Announcements

#### Happy New Year from Power BI

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