Count or IF STATEMENT WITH TABLE SUMMARY details

[malguera]

Hello Team,

99.9% sure is a rookie mistake here, but I'm trying to create a simple measure to get a count of Accounts where have less than X$ Amount.
The tricky piece is that this should calculate (do the count) based on what is displayed on the Table Visualization (like the pivot summary) not on the raw data.

Summary Table Displays:



Note: $ Sales on the above table is the aggregation on orders, products and $ amounts associated to a particular account

Can you give me a hand here?
I tried a couple of options: a. creating a flag column first for the accounts that have less than $10 and creating the count for the flag and B. Creating the count measure directly using a filter for $ Sales less than $10 but is not working either.

Thanks,
M

[Stachu]

can you try the following:

Sales Filtered = 
VAR AccountsSalesFiltered= FILTER(ADDCOLUMNS(SUMMARIZE(Table,Table[Account Name]),"Val",[$ Sales])),[Val]>10)
RETURN
CALCULATE([$ Sales],AccountsSalesFiltered)

[malguera]

@Stachu Thanks for your response. Your suggestion is giving me the Total Aggregation where the amount is greater than 10 (25 from the example I provided), since is the combination of Account  B and Account D totals.

I'm looking for the count of accounts where the summarized value by each account is less than 10 in the example, so it should be 2... since only 2 accounts have an aggregation of less than 10.

Sorry if I was not clear enough on my problem statement.

M 

[Stachu]

then it should be something like this:

Sales Filtered = 
VAR AccountsSalesFiltered= FILTER(ADDCOLUMNS(SUMMARIZE(Table,Table[Account Name]),"Val",[$ Sales])),[Val]<10)
RETURN
COUNTROWS(AccountsSalesFiltered)

[Phil_Seamark]

Hi @malguera

Would this work as a calculated measure?

Measure = 
    COUNTROWS(
        FILTER(
            'Table1',
            [$ Sales]<10
            )
        )

[malguera]

Hi Phil,

Thanks for your quick response! Unfortunately it didn't work, since is showing the count of rows from the rawdata, not from the summary table

[Phil_Seamark · Accepted solution]

Hi @malguera

Please try this

Measure = 
    COUNTROWS(
        FILTER(
            VALUES('Table1'[Account Name]),
            CALCULATE(SUM([$ Sales]))<10
            )
        )

[malguera]

@Phil_Seamark Thanks! looks is working fine!