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