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.
Solved! Go to Solution.
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.
The correct value for the category "< $5M" is $360,494,241.33 which you can manually check by downloading the table from the Data tab in Power BI and calculating in Excel.
The error in your test matrix is that you set the filter on the "Sum" column which will first be grouped & summed by Category, so if you have a category listed more than once and the sum of all of them is >$5M then it will not be included. Example: Category 89 has 5 rows, and each row is <$5M, however when you group & sum then Category 89=$12.4M so it will not be included in your test pivot.
Also, in your SWITCH statement you should change "> 5000000" to ">= 5000000" else any value in the data that is exactly 5M will return the last (default) value of BLANK().
@Anonymous - The value of $360,494,241.33 for <5M on the raw data is correct and known to me, but that is not what am solving for, I want to calculate <5M after the data is Pivoted in essence over the category column which will then yield $44,807,555.54 which is what is required and correct.
Also, the test matrix that I built out was for demo purpose only to showcase what the intended value of <5M bucket should be ( $44,807,555.54 ) vs. what it is ($360,494,241.33). As the final pie chart distribution is to be based on the Calculated classification column of [Sum] column over the category column. Also, changing the SWITCH statement, to ">=5000000" from "> 5000000" did not render any change in the distributions.
@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.
you should be abel to use a switch statement
@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.
Learn how to create your own user groups today!
Click here to read more about the November 2021 Updates!
Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.