Forum Discussion

NLC's avatar
NLC
Frequent Visitor
9 years ago
Solved

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 ...
Need Help
  • dedelman_clng's avatar
    dedelman_clng
    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

dedelman_clng's avatar
dedelman_clng
Community Champion
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