Forum Discussion
NLC
9 years agoFrequent Visitor
How do you slice data into quartiles and quintiles
Hi all, I am trying to slice my data into Quartiles and Quintiles based on the value of my revenue column. I am only dealing with only one table. The data set I am dealing with is over a million ...
- 9 years ago
You cannot use a measure in a slicer, so you may have to create a disconnected dummy table to control the quartile that you want to look at (see this link for the general idea: https://community.powerbi.com/t5/Video-Tips-and-Tricks/Turn-Measures-On-and-Off-Inside-a-Chart-with-CONTAINS/m-p/151045#M16 )
For calculating the quartiles in your data set, this code should work for you:
Quartile = var FirstQ = CALCULATE(PERCENTILE.INC(Table1[Column1], .25), ALL(Table1[Column1])) var SecondQ = CALCULATE(PERCENTILE.INC(Table1[Column1], .50), ALL(Table1[Column1])) var ThirdQ = CALCULATE(PERCENTILE.INC(Table1[Column1], .75), ALL(Table1[Column1])) var ThisVal = Min(Table1[Column1]) return IF(HASONEVALUE(Table1[Column1]), IF(ThisVal <= FirstQ, 1, IF(ThisVal > FirstQ && ThisVal <= SecondQ, 2, IF(ThisVal > SecondQ && ThisVal <= ThirdQ, 3, 4) ) ) )
Hope this helps
David
NLC
9 years agoFrequent Visitor
Thanks David! It was a huge help, and was able to find a way to slice my data the way I wanted to.
snaruma
8 years agoRegular Visitor
Hi,
I know this post was a long back ago. But can you help me understand how were you able to use a disconnected table to slice the data using the quartile measure in a slicer?
Appreciate your response.
Thanks!