Forum Discussion

CharlotteCity12's avatar
CharlotteCity12
Microsoft Employee
9 years ago
Solved

Division is not working

PBII 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:

4 Replies

  • v-sihou-msft's avatar
    v-sihou-msft
    Microsoft Employee
    9 years ago

    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