cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Frequent Visitor

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.

 

salebycode1.JPG

 

Thanks

Kavitha

1 ACCEPTED SOLUTION

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..!

View solution in original post

5 REPLIES 5
Super User II
Super User II

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




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

salebycode2.JPG

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.

 

salebycode1.JPG

 

 

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

 

 

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])




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

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..!

View solution in original post

Awesome glad you got it working.




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Wave Release 2

Check out the updates in Power BI.

Overview of Power BI 2020 release wave 2!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors