Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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

Helpful resources

Announcements
March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.