Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Microsoft Power BI Community
- Forums
- Get Help with Power BI
- Desktop
- Re: Creating IF Statement Over Category

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Creating IF Statement Over Category

07-18-2019
10:10 AM

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

Solved! Go to Solution.

1 ACCEPTED SOLUTION

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

07-22-2019
07:43 PM

Hi,

You may download my PBI file from here.

Hope this helps.

Regards,

Ashish Mathur

http://www.ashishmathur.com

https://www.linkedin.com/in/excelenthusiasts/

10 REPLIES 10

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

07-20-2019
07:21 PM

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/

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

07-22-2019
11:15 AM

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

07-22-2019
07:43 PM

Hi,

You may download my PBI file from here.

Hope this helps.

Regards,

Ashish Mathur

http://www.ashishmathur.com

https://www.linkedin.com/in/excelenthusiasts/

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

08-02-2019
06:55 PM

You are welcome.

Regards,

Ashish Mathur

http://www.ashishmathur.com

https://www.linkedin.com/in/excelenthusiasts/

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

07-22-2019
03:08 PM

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

07-22-2019
03:25 PM

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

07-22-2019
07:58 AM

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

07-18-2019
10:51 AM

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

)

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

)

Proud to be a Super User!

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

07-20-2019
11:55 AM

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

Featured Topics

Top Solution Authors

User | Count |
---|---|

271 | |

152 | |

56 | |

52 | |

44 |

Top Kudoed Authors

User | Count |
---|---|

256 | |

169 | |

69 | |

67 | |

53 |