Forum Discussion
Creating IF Statement Over Category
Hello All,
I am running into an issue trying to write a measure/calc. column which is basically an IF statement that classifies items:
Classification = IF([Sum] < 5000000 , "<$5M",
IF([Sum] > 5000000 && [Sum]<= 10000000, "$5M - $10,",
IF([Sum] > 10000000 &&[Sum] <= 15000000,"$10M - $15M",
IF([Sum] > 15000000 &&[Sum] <= 20000000,"$15M - $20M",
IF([Sum] > 20000000, ">$20M", "Blank")))))
The above works but not as intended as I want each of those IF Statements over a [Category] column, which in the end is used to create a Pie Create using the [Classification] defined above in Legend and the [Sum] column as the value of Grand Total % in the Values section. In excel its basically putting a number filter on the [Sum] column and then doing a =Subtotal(9, Referencing the cells of the filtered values) and then dividing that value by the sum of the entire Sum column and formatting that into a %.
Any idea of how I should write the measure for that or go about it.
Thank You
10 Replies
you should be abel to use a switch statement
Classification =
SWITCH (
TRUE (),
[Sum] < 5000000, "<$5M",
[Sum] > 5000000
&& [Sum] <= 10000000, "$5M - $10,",
[Sum] > 10000000
&& [Sum] <= 15000000, "$10M - $15M",
[Sum] > 15000000
&& [Sum] <= 20000000, "$15M - $20M",
[Sum] > 20000000, ">$20M",
BLANK ()
)vanessafvg - What happens is that I need the IF or Switch statement to also factor in a category column.
Here is a screenshot of what I am trying to achieve, on the left is the [Classification] column which is a calc. column which is based on the conditions laid out (eg:- [Sum] < 5000000 , "<$5M" etc. ) and the [Sum] Column which gives a total for the <$5M slot (360..) as different from the right table which is only for <$5M.
The right table has the intended result as it has the [Sum] column and also the [Category] column (addition of this column is key which gives the intended result) and I have added a filter on the [Sum] column (via the filter pane) for less than $5M and that gives a total of $44,807 which is the desired output. As in the end, I want to divide that by the total of the [Sum] column and do the % Grand Total in a pie chart. In short, the calc column should have the condition based on the [Sum] column but consider/factor the [Category] column too.
Hi,
Share some data and show the exact expected result in a Table format. Once the Table format is ready, we can switch to a Pie visual.
Ashish_Mathur - Will prep the sample data, but before that, I noticed that my IF or Sqitch statement both are working weirdly. So for the first condition which is checking if the [Sum] is less than 5000000 (5M) it's still putting values which are over 5M in that bucket for some reason. The data type of the [Sum] is set to Decimal Number which in itself I find crazy to occur.
Ashish_Mathur - Here is the link to the file which contains the sample data with few details listing out the issues which you can download. This has been so annoying, I feel I am missing something very basic here. Thanks a lot in advance Ashish!
