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 all,
Trying to find a solution to my issue.
I am trying to count the number of parts by a group but displayed a sum of another group but cant get the distinctcount straight in my mind. Example below
I want to count the number of unique parts in a quote, then display that as a sum for the month. If you note the last 3 lines are the same part, I want to count that part as one occurance for quote 1017P and one occurance for quote 1022P which would ultimatly sum to 2 for the month of Nov.
DATE | QUOTE_NO | TRIMMED_NUMBER |
1/11/2018 | 1017P | CPGVE-04011 |
1/11/2018 | 1017P | CPGVE-04011 |
1/11/2018 | 1017P | CPGVE-04110 |
1/11/2018 | 1017P | CPGVE-07011 |
1/11/2018 | 1017P | CPGVE-10010RH |
1/11/2018 | 1017P | CPGVE-10010RH |
1/11/2018 | 1017P | CPGVE-20010 |
1/11/2018 | 1017P | CPGVE-21031LH |
1/11/2018 | 1017P | CPTTH-04020 |
1/11/2018 | 1022P | CPTTH-04020 |
1/11/2018 | 1022P | CPTTH-04020 |
Expected output
Month | Unique Parts Per Quote |
Nov-18 | 8 |
Thanks kindly
Solved! Go to Solution.
Hi @JuiceX,
You can use the below measure to get the desired result. Replace 'Table1' with your table name.
Measure = CALCULATE(COUNTX(SUMMARIZE(Table1,Table1[QUOTE_NO],Table1[TRIMMED_NUMBER]),CALCULATE(VALUES(Table1[TRIMMED_NUMBER]))))
I have tested the same and I got the below result
If this helped you, please mark this post as an accepted solution and like to give KUDOS .
Regards,
Affan
Hi @JuiceX,
You can use the below measure to get the desired result. Replace 'Table1' with your table name.
Measure = CALCULATE(COUNTX(SUMMARIZE(Table1,Table1[QUOTE_NO],Table1[TRIMMED_NUMBER]),CALCULATE(VALUES(Table1[TRIMMED_NUMBER]))))
I have tested the same and I got the below result
If this helped you, please mark this post as an accepted solution and like to give KUDOS .
Regards,
Affan
Excellent, works, thankyou!!
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 |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
87 | |
64 |