Forum Discussion

KChandra's avatar
KChandra
Frequent Visitor
9 years ago
Solved

Division over a SWITCH column

Hello All

 

I am trying to create a division calculation on top a numeric column derived using a switch statement.

Codeval = SWITCH('Table'[Index], "1", 1, "2", 1, "3",1, "4",1, 0).

 

"Index" column is nested index created and defined as text for avoiding auto aggregation of the index numbers in previous calculations.

 

Expecting results like "AvgSalesby4" column in the below screen shot. AVGsale formula performs SumofSale/1 instead of Sumsale/4, please let me know what is the issue. Tried summarize function by creating a table was not helpful.

 

 

Thanks

Kavitha

Need Help
  • KChandra's avatar
    KChandra
    9 years ago

    Hi Guavaq,

     

    I tried this following by creating it as a measure as per your ealier suggestion

     

    NewMeasure = Divide(sum(Sumofsales), count(Codeval),0) worked..!

     

    Thank you..!

5 Replies

  • GilbertQ's avatar
    GilbertQ
    Super User
    9 years ago
    Hi there is your Codeval a calculated column or a measure?

    Base on your image above the AvgSale should be the same as your AvgSale4
    • KChandra's avatar
      KChandra
      Frequent Visitor
      9 years ago

      Hi guavaq,

       

      Thanks for the  reply, i am assuming its aggregate attribute. i am new to Power BI but have worked on various other BI technologies. based on its properties i see default summarization as "sum" and icon symbol is same of that of the other numeric metrics like SumSales.

       

       

       

      If i tried to use SUM() on Codeval = Sum(SWITCH('Table'[Index], "1", 1, "2", 1, "3",1, "4",1, 0)), i get this error - The SUM function only accepts a column reference as an argument.

       

      Thanks

      Kavitha

       

       

      • GilbertQ's avatar
        GilbertQ
        Super User
        9 years ago
        Hi there, to do a Sum, you have to have the column already created.

        I would suggest creating your Calculated Column first (or rather create your column in the Query Editor, where you can easily use a Conditional Column)

        Then once you have your Column, you can then go with the measure. MeasureName = sum('Table1'[Column])