Forum Discussion
Create Quartiles by Groups and Removing Specific Values
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.
5 Replies
- AlexisOlson
Super User
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] ) ) ) 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 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.
