Unable to divide measure by a column in SSAS Model

[javaid355]

I am trying to divide a measure [credit-Debit ] by a column "job weekly scheduled hours" in order to get the houlry rate . The data is in SSAS layer. I can only derive measures . Calculated columns are not allowed. ( igonore the negative sign in [credit - debit].

I'd appreciate any feed backs.

Thanks

Javaid

[v-lili6-msft]

hi  @javaid355 

Since you are using Live connection in power bi, it is live connecting to a data model, so you could just create report level measure in power bi, all other modeling like create column/table,etc needs to be done in ssas.

https://radacad.com/live-connection-when-power-bi-comes-hybrid

For your case,just create a measure as below to get your requirement:

Result=DIVIDE([credit-Debit ] , SUM(Table[Job Scheduled Weekly Hour]))

Regards,

Lin 

[javaid355]

Hi Lin, 

I am not getting the desired results by using this formula.

Please let me know your thoughts.

Thanks.

Jav

[amitchandak]

@javaid355 ,

Divide should be measure only  

Measure = divide(sumx(Table,abs(Table[credit - debit])),sum(Table[Job Scheduled Weekly Hour]))

or

AvergaeX(Table,divide(abs(Table[credit - debit]),Table[Job Scheduled Weekly Hour]))

[javaid355]

Hey Amit,

Both of your suggestions are not giving the correct hourly rate. ( just a reminder that [credit-debit] is a calculated measure, not a column.

Thanks.