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

  • 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 ?