cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
nirvana_moksh Established Member
Established Member

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

Accepted Solutions
Super User
Super User

Re: Creating IF Statement Over Category

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
vanessafvg Super Contributor
Super Contributor

Re: Creating IF Statement Over Category

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

 


Did I answer your question? Mark my post as a solution.
Proud a to be a Datanaut!
nirvana_moksh Established Member
Established Member

Re: Creating IF Statement Over Category

@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

Super User
Super User

Re: 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.


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

Re: Creating IF Statement Over Category

@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

Highlighted
nirvana_moksh Established Member
Established Member

Re: Creating IF Statement Over Category

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

hymieho2 Frequent Visitor
Frequent Visitor

Re: Creating IF Statement Over Category

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

nirvana_moksh Established Member
Established Member

Re: Creating IF Statement Over Category

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

Super User
Super User

Re: Creating IF Statement Over Category

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

nirvana_moksh Established Member
Established Member

Re: Creating IF Statement Over Category

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

Helpful resources

Announcements
New Ranks and Rank Icons in 2020

New Ranks and Rank Icons in 2020

Read the announcement for more information!

New Kudos Given Badges Coming

New Kudos Given Badges Coming

We're rolling out new Kudos Given badges. Find out how many Kudos you've given.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Kudoed Authors (Last 30 Days)