Forum Discussion

joshs444's avatar
joshs444
Frequent Visitor
3 years ago
Solved

Complex Calculated Column Trouble

I have a calculated column with item sales as a percentage of total sales. It is just the total sales for each item divided by the total sales overall).


I am trying to create a new column, based on the Percent Sales column.

 

I would like the column say "TOP" if the percentage makes up the top 20% of sales, and "BOTTOM" if the item makes up the bottom 20% of sales.

 

I am having a hard time wrapping my head around how to write this as a calculated column.

 

Any help is appreaciated.

 

Here are details for the Percent Sales calculated column:

 

Percent Sales =
VAR total =
SUMX('Invoiced Orders','Invoiced Orders'[Unit Price]*'Invoiced Orders'[Quantity])
return

DIVIDE([Sum Sales - Invoided Orders],total,0)
 
Sum Sales - Invoided Orders =
SUMX('Invoiced Orders','Invoiced Orders'[Unit Price]*'Invoiced Orders'[Quantity])
 
 
Thanks in advance!!!
Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi joshs444 ,

     

    I tried to reproduced it.

     You need to modify the [Sum Sales - Invoided Orders] as 

    Sum Sales - Invoided Orders = [Quantity]*[Unit Price]

    [Percent Sales] now is good.

    And it's the column to dispaly "TOP" and "BOTTOM".

    Type = IF([Percent Sales]>0.2,"TOP","BOTTOM")

    Best Regards,

    Stephen Tao

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.           

     

     

2 Replies