Calculate Quartiles based on filters/criterias

[powerBIpeon]

I want to only calculate the quartiles if the account meets the criteria below.

• Account is at 100 to 1,000 users
• Revenue is between $0 and $100,000

I'm currently using this calculated column, but it is calculating all of the data set rather then just the critera above. I tried including additional filters to the formula, but the results are the same. Should use a different approach?

Quartile = 
var low = PERCENTILEX.INC(
     Filter(
            'Assets',
            ISBLANK('Assets'[Revenue]) = FALSE()),
    ),
    'Assets'[Revenue], 0.25
)
var mid = PERCENTILEX.INC(
     Filter(
            'Assets',
            ISBLANK('Assets'[Revenue]) = FALSE()),
    ),
    'Assets'[Revenue], 0.5
)
var high = PERCENTILEX.INC(
     Filter(
             'Assets',
            ISBLANK('Assets'[Revenue]) = FALSE()),
    ),
    'Assets'[Revenue], 0.75
)
Return 
IF([Revenue] <= low, 1, IF([Revenue] <= mid, 2, IF([Revenue] <= high, 3, 4)))

[CheenuSing]

Hi @powerBIpeon 

You will have to filter the table for the criteria and then on the filtered set compute.

Please share some data and the expected output.

Cheers

CheenuSing

[powerBIpeon]

@CheenuSing 

Results:

Q0 = $37,800

Q1 = $39,476.56

Q2 = $42,857.25

Q3 = $60,179.09

Q4 = $79,704.00

Link to data table: 

https://www.dropbox.com/s/qytdjahgwnakfak/quartileexample.pbix?dl=0

Thank you in advance!

[CheenuSing · Accepted solution]

Hi @powerBIpeon,

I went through the file again. I did the following.

1.  I created filters on table Assets for User Band and Revenue as per your criteria in the calculated column Quartile computation.

2.  Then in the table output to show Quartile and Revenue,

         a )I changed Revenue not to be summarized.

         What this means is that it shows which quartile each revenue value belongs to.  In your original

           table it was showing the sum of revenue column under a quartile.

        b) Also set the Revenue in the Visual filter to less than 100,000.

        c) Added USerBand in the Visual filter and set it to  1 to 1000. This will elimnate showing  other values in the userband.

3. I also created a disconnected table Quartile with values as 1 to 4.

4. created a measure called ShowValue . please go through it.

5. Created a table visual with values as Quartile from Quartile table and ShowValue as values.

I have attached the pbix file for your reference.

Let me know if this works.

Cheers

CheenuSing

3. I created the Quartile measures - Quartile0 to Quartile 4 and plotted them on a card visual.