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

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
Turambar
Frequent Visitor

QoQ Sales Decile Within Category

I’m having trouble figuring out how to do this. I want a measure that returns the decile for each item within a given product category based off of [Current QoQ]. The QoQ value will be negative for a fair number of items. Any help would be greatly appreciated.

1 ACCEPTED SOLUTION
v-kaiyue-msft
Community Support
Community Support

Hi @Turambar ,

 

To calculate the decile, you can use the PERCENTILEX.INC function, which returns the percentile of the expression calculated for each row in the table.The INC function returns the number that specifies the percentile. For example, if I have the numbers 0 and 100 in my dataset, the 20th percentile value would be 20. the 50th percentile value would be 50, the 70th percentile value would be 70, and you could calculate what the 100th percentile value would be.

 

If you write an expression to calculate the 70th percentile value, this also provides the seventh upper decile.

Rank = PERCENTILEX.INC(<table>, <expression>,.70)


More detailed information can be found at the link:

Calculating Quartiles with DAX and Power BI | Data and Analytics with Dustin Ryan (sqldusty.com)

PERCENTILEX.INC function (DAX) - DAX | Microsoft Learn

 

If your Current Period does not refer to this, please clarify in a follow-up reply.

 

Best Regards,

Clara Gong

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

1 REPLY 1
v-kaiyue-msft
Community Support
Community Support

Hi @Turambar ,

 

To calculate the decile, you can use the PERCENTILEX.INC function, which returns the percentile of the expression calculated for each row in the table.The INC function returns the number that specifies the percentile. For example, if I have the numbers 0 and 100 in my dataset, the 20th percentile value would be 20. the 50th percentile value would be 50, the 70th percentile value would be 70, and you could calculate what the 100th percentile value would be.

 

If you write an expression to calculate the 70th percentile value, this also provides the seventh upper decile.

Rank = PERCENTILEX.INC(<table>, <expression>,.70)


More detailed information can be found at the link:

Calculating Quartiles with DAX and Power BI | Data and Analytics with Dustin Ryan (sqldusty.com)

PERCENTILEX.INC function (DAX) - DAX | Microsoft Learn

 

If your Current Period does not refer to this, please clarify in a follow-up reply.

 

Best Regards,

Clara Gong

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

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