Division is not working

[CharlotteCity12]

I have a Metric Column.

If Metric Column = 1 then sum amount 1 column,
if metric column = 2 then sum amount 2 column,
if metric column = 3 then sum amount 3 column and divide by sum of Div column

Totals11 = sum(main[Amount1])

Totals22 = sum(main[Amount2])

Totals33 = sum(main[Amount3])

DivTotal = SUM(main[Div])

divcoln = SUM(main[Amount3])/SUM(main[Div])

mainfield = IF(main[Metric]=1,[Totals11],IF(main[Metric]=2,[Totals22],IF(main[Metric]=3,[divcoln],0)))

 On a chart the MainField column is summing metric 3

See PBI Chart:

[v-sihou-msft]

@CharlotteCity12

In this scenario, it's much better to use a calculated column to include different value based on Metric in your main table instead of creating multiple measures. Just add a calculated column like below:

Amount = SWITCH(main[Metric],1,main[Amount1],2,main[Amount2],3,main[Amount3],BLANK())

Then you just need to create one measure to sum above column.

Totals = SUM(main[Amount])

Your mainfield can be like:

mainfield = IF(main[Metric]=3,[Totals]/[DivTotal],[Totals])

Regards,

Simon Hou

[dannio]

It is not working as I need.

Amount = SWITCH(main[Metric],1,main[Amount1],2,main[Amount2],3,main[Amount3],BLANK())

Totals = SUM(main[Amount])

mainfield = IF(main[Metric]=3,[Totals]/[DivTotal],[Totals])

Same issue as orginal post

'

See link to PBIX:

https://1drv.ms/u/s!AvFqXklXcyRRg_F5uq_9jWgtSZCnug

link to PBIX

[v-sihou-msft · Accepted solution]

@dannio

You should have the [Totals] measure divided by [DivTotals].

mainfield = IF(main[Metric]=3,[Totals]/main[DivTotals],[Totals])

Regards,

[tringuyenminh92]

Hi @CharlotteCity12,

Could i have your sample data file to check in my local?