How do you slice data into quartiles and quintiles

[NLC]

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 rows, so I rather create a measure rather than create a column. (You can create slicers from measures right?) What I have tried to do is use the Switch function. This is what I have so far below:

Quart = SWITCH(TRUE,

x<=PERCENTILE.INC(PUB2015[Revenue], 0.25), 1,

x<=PERCENTILE.INC(PUB2015[Revenue], 0.50), 2,

x<=PERCENTILE.INC(PUB2015[Revenue], 0.75), 3,

4)

However I don’t know what I should use for where the “x” are to make it work. If someone could help me figure this out, wheather it is using the switch function, or something else I am all ears.

Thank you!

[dedelman_clng · Accepted solution]

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

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

[MAAbdullah_47]

hi @dedelman_clng does it works for measures ?

[ThomasPHT]

Hello I have been trying to apply the same measure for my data, unfortunately it always return 1 for each record

Any ideas ? 

[Anonymous]

Hi-- yes, Daniel. If you could expand on how you connected the measure to the dummy table that would be very helpful. My guess is the measure you posted above needs to be modified to refer to the table. 

[NLC]

Thanks David! It was a huge help, and was able to find a way to slice my data the way I wanted to.

[snaruma]

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!