GROUPBY with Greater than or equal to

[Anonymous]

Hello, below is a small subset on how my Data Table and Lookup Tables are organized. My actual data table has 220k rows!

I will attached the Power BI File along with the Excel files.

The bottom three tables are my Model consisting of my Database, Class Lookup table (Relationship based on Model) and Name Lookup 100k+ Lookup table (Realtionship based on Company Name). 

You will notice that my Name Lookup 100k+ is composed of two rows which have the mispelled Company Name is certain instances and the Correct Company name however the list was created only for Company names which have an aggregated Amount of $100,000 or more. That represent about 1300 rows in my real data set out of the 220,000 rows. It would have been to exhausting of an excercise to correct the company names for aggregate amounts of 100,000 or less. 

Solutions Required. 1) The first Solution I require actually works without a Dax. I require to know which Model Class per Corrected Company Name equal 100,000 or more as displayed in my >= 100K (Correct Answer Table). I still require a Dax that accomplishes this so I created this Dax which outputs the Group by Dax Table in the picture. I need a modifier to eliminate a Model Class per Correct Company name that only shows a 100,000 or more. You will understand in a minute why I need a Dax for this.

Sales (100k+) = 
CALCULATE(
    SUM(Database[Amount]),
    GROUPBY('Class Lookup','Class Lookup'[Model Class]),
    GROUPBY(CompanyNameLookup,CompanyNameLookup[Correct Company Name])
)

2) The second solution I require is a Dax to aggregate the Model Class per Company Name that is less than $100,000, which was not aggregated in my 100K (Correct Answer Table)  The correct answer would appear in the <100K Correct Answer Table (Which I filtered manually). You will notice I do not use Correct Company Name but just Company name because I don`t actually need the corrected names in my output, and these are only used for a count, unlike my 100k+ where I need the correct names to appear.  

https://drive.google.com/file/d/1aBlLiyKAkLDFVIxDrSKZIPN2QoY7Ocw_/view?usp=sharing 

[amitchandak]

@Anonymous , For Greate than 100K try a measure like

sumx(filter(summarize(Database,'Class Lookup'[Model Class],CompanyNameLookup[Correct Company Name], "_1",SUM(Database[Amount])),[_1]>100000),[_1])

[Anonymous]

Thank you. Actually it is for >= 100K so I changed that but your solution is still creating the output for the Single Piston Class under 100,000. The correct solution should not include it.

[Anonymous · Accepted solution]

Solution here:

Solved: Re: Summarize Dax (without summarizing text string... - Microsoft Power BI Community