Forum Discussion
Creating IF Statement Over Category
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 - 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!
Like always thanks for the wonderful help Ashish_Mathur! This worked :)
You are welcome.
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.