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.
Hi
I have a dataset containing, amongst much other, following information:
Supplier name Sum invoice
Supplier 1 500
Supplier 2 1000
Supplier 3 500
Supplier 4 2000
Supplier 5 4000
Supplier 1 500
Supplier 2 1000
Supplier 3 500
Supplier 4 2000
Supplier 5 4000
I want a unique count of suppliers with total sum of invoices> 1999. Using distinct count and a visual level filter > 1999 gives me a unique count of suppliers for each invoice greater than 1999 = 2 (supplier 4 and 5). The correct count would be 3 (supplier 2 with a total of 2000, supplier 4 with a total of 4000, and supplier 5 with a total of 8000). Preferably, I would have this as a measure, enabeling my to further viusalize the count as I slice down in my report at different departments, categories, etc (which are other columns linked to the ones illustrated above). Does anyone know how to do this, if possible?
Solved! Go to Solution.
Hi @Gurote
Either of these should work. I'm assuming [Sum invoice] is a column.
The 2nd pattern is useful if you want to filter on those same suppliers, but change the DISTINCTCOUNT to something else
Distinctcount of Suppliers with Total Invoices over 1999 = COUNTROWS ( FILTER ( VALUES ( YourTable[Supplier name] ), CALCULATE ( SUM ( YourTable[Sum invoice] ) ) > 1999 ) ) Distinctcount of Suppliers with Total Invoices over 1999 = CALCULATE ( DISTINCTCOUNT ( YourTable[Supplier name] ), FILTER ( VALUES ( YourTable[Supplier name] ), CALCULATE ( SUM ( YourTable[Sum invoice] ) ) > 1999 ) )
Hi @Gurote
Either of these should work. I'm assuming [Sum invoice] is a column.
The 2nd pattern is useful if you want to filter on those same suppliers, but change the DISTINCTCOUNT to something else
Distinctcount of Suppliers with Total Invoices over 1999 = COUNTROWS ( FILTER ( VALUES ( YourTable[Supplier name] ), CALCULATE ( SUM ( YourTable[Sum invoice] ) ) > 1999 ) ) Distinctcount of Suppliers with Total Invoices over 1999 = CALCULATE ( DISTINCTCOUNT ( YourTable[Supplier name] ), FILTER ( VALUES ( YourTable[Supplier name] ), CALCULATE ( SUM ( YourTable[Sum invoice] ) ) > 1999 ) )
Thank you! This solved my problem
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 |
---|---|
115 | |
99 | |
86 | |
70 | |
62 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |