Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
Hi @malguera
Please try this
Measure = COUNTROWS( FILTER( VALUES('Table1'[Account Name]), CALCULATE(SUM([$ Sales]))<10 ) )
can you try the following:
Sales Filtered = VAR AccountsSalesFiltered= FILTER(ADDCOLUMNS(SUMMARIZE(Table,Table[Account Name]),"Val",[$ Sales])),[Val]>10) RETURN CALCULATE([$ Sales],AccountsSalesFiltered)
@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
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)
Hi @malguera
Would this work as a calculated measure?
Measure = COUNTROWS( FILTER( 'Table1', [$ Sales]<10 ) )
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
Hi @malguera
Please try this
Measure = COUNTROWS( FILTER( VALUES('Table1'[Account Name]), CALCULATE(SUM([$ Sales]))<10 ) )
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
97 | |
82 | |
67 | |
61 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |