cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
nirvana_moksh
Impactful Individual
Impactful Individual

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

1 ACCEPTED SOLUTION

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

10 REPLIES 10
Ashish_Mathur
Super User
Super User

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

Like always thanks for the wonderful help @Ashish_Mathur! This worked 🙂

You are welcome.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

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.



Capture1.PNG

vanessafvg
Super User
Super User

@nirvana_moksh 

 

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

 





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




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

 

Classification Test.PNG

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.