Create Quartiles by Groups and Removing Specific Values

[brabby_dabby]

Hello, this questions has been answered, but this is a variation that I need a little more help on.

I have a dataset that looks like this:

Time	Class1	Class2	Subclass1	Subclass2	Vendor	SKU	Rate
12	1	REF	11	FDR	A	111	.089
12	1	REF	11	FDR	B	112	.112
12	1	REF	11	FDR	C	113	.121
12	1	REF	11	FDR	D	121	.080
12	1	REF	11	FDR	E	122	.065
12	1	REF	11	FDR	F	131	.050
12	1	REF	12	SS	A	141	.112
12	1	REF	12	SS	B	151	.131
12	1	REF	12	SS	C	161	.054

I have a lot more Times, Classes, Subclasses, SKUs, and Vendors...

I want to calculate the first and third quartiles.

Quartile1, for example, I want to group all the 'FDR' values together that are under 'SUBCLASS2' that have the same 'Time' value and calculate the first Quartile.

This is where it gets confusing: For the first row in 'Subclass' = 'FDR' under Vendor A, I would like to calculate the 1st Quartile, BUT, instead of including 'Vendor A', I would like to remove that vendor from the quartile calculation. Therefore, for Time=12, Class2 = REF, Subclass2 = FDR, and Vendor = A, the quartile should be 0.065.

Time	Class1	Class2	Subclass1	Subclass2	Vendor	SKU	Rate	Quartile1	Quartile3
12	1	REF	11	FDR	A	111	.089	0.065	0.112
12	1	REF	11	FDR	B	112	.112	0.065	0.089
12	1	REF	11	FDR	C	113	.121	0.065	0.089
12	1	REF	11	FDR	D	121	.080	0.065	0.112
12	1	REF	11	FDR	E	122	.065	0.080	0.112
12	1	REF	11	FDR	F	131	.050	0.080	0.112

And for Vendor B, it would be the same, but I would like to remove vendor B from that Subclass/Time grouping. And so on...

I started by creating a table and then using the following equation to create the quartiles:

Quartile1 = SUMMARIZE(dataset, dataset[Class1],dataset[Class2],dataset[Subclass1], dataset[Subclass2],dataset[Vendor],"Q1", PERCENTILE.INC(dataset[rate],0.25))

But I'm not sure how to tell Power BI that I want to to remove that specific vendor when calculating Quartile1.

Thank you for your help. I am not very familiar with DAX and have been trying to figure this out for a while without any success.

[Anonymous]

Hi @brabby_dabby ,

I am sorry. I also don't know what Temp_Table and Main_Table are. How to get 0.65 and 0.8. Why the rate data is not the same? Can you provide more detailed data and desired output?

Best Regards

Community Support Team _ polly

[brabby_dabby]

@AlexisOlson @Anonymous 

I am sorry, I changed the table names on you:

• Temp_Table = Quartile1 Table. Because I'm summarizing I have to make a summary table and can't do calculated column or measure.
• Main_Table = dataset

The 0.65 and 0.8 are the Quartiles that I calculated manually (I have thousands of rows so this isn't a possibility on my data). For example, In a normal scenario, if I am calculating the first Quartile for the FDR Subclass for Time 12, normally, I would just group everything from 'dataset':

Time	Class1	Class2	Subclass1	Subclass2	Vendor	SKU	Rate
12	1	REF	11	FDR	A	111	.089
12	1	REF	11	FDR	B	112	.112
12	1	REF	11	FDR	C	113	.121
12	1	REF	11	FDR	D	121	.080
12	1	REF	11	FDR	E	122	.065
12	1	REF	11	FDR	F	131	.050

But, for my reasons, I want to calculate the first quartile for Vendor A, but I want to remove Vendor A from the calculation so the 'Rate' that I'm using for this particular grouping is only using the following Vendors (For FDR subclass and Time 12): 

Time	Class1	Class2	Subclass1	Subclass2	Vendor	SKU	Rate
12	1	REF	11	FDR	B	112	.112
12	1	REF	11	FDR	C	113	.121
12	1	REF	11	FDR	D	121	.080
12	1	REF	11	FDR	E	122	.065
12	1	REF	11	FDR	F	131	.050

And the same logic applies for all the other vendors. When I want to calculate the first quartile for vendor B, I want to remove Vendor B's rate from the calculation. And for Vendor C, I want to remove Vendor C's rate from the calculation...etc.

[brabby_dabby]

@AlexisOlson The equation you presented looks like it works in Temp_Table. But when I try a LOOKUPVALUE to import Temp_Table[Quartile1] to the Main_Table, I'm getting a circular depency error.

[AlexisOlson]

Sorry, I don't know what Temp_Table and Main_Table are. The only table you mention in the post is 'dataset'.

[AlexisOlson]

You could try REMOVEFILTERS:

Quartile1 =
SUMMARIZE (
    dataset,
    dataset[Class1],
    dataset[Class2],
    dataset[Subclass1],
    dataset[Subclass2],
    dataset[Vendor],
    "Q1",
        CALCULATE (
            PERCENTILE.INC ( dataset[rate], 0.25 ),
            REMOVEFILTERS ( dataset[Vendor] )
        )
)