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 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!

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

6 Replies

  • 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

    • NLC's avatar
      NLC
      Frequent Visitor
      9 years ago

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

      • snaruma's avatar
        snaruma
        Regular Visitor
        8 years ago

        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!

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      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. 

    • ThomasPHT's avatar
      ThomasPHT
      Regular Visitor
      6 years ago

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

       

      Any ideas ?