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 everyone,
I have a table with 2 columns: 'Code' and 'Values'. The Code can be repeated.
The Values column is the same for each code, so 2 Values for 'CodeA' are equal to each other, and 2 values for 'CodeB' are also the same.
What I need is a measure to sum ONE TIME each value for each code, so in the example of the table below I would only need to sum 1 value for CodeA and 1 value for CodeB and the value for CodeC (3+5+7)
I thought a way that is using an auxiliar column named 'Unique' which is '1' if it is the first time the Code appears, and '0' if not, and use a CALCULATE function later. But I couldn't find a way to make this 'Unique' columns.
The Unique column would be:
Code Value Unique
CodeA 3 1
CodeA 3 0
CodeB 5 1
CodeB 5 0
CodeC 7 1
Thanks in advance!
Solved! Go to Solution.
Here's the MEASURE that will do this for you...
Measure = DIVIDE ( SUM ( 'Table'[Value] ), CALCULATE ( COUNTA ( 'Table'[Code] ), ALLEXCEPT ( 'Table', 'Table'[Code] ) ), 0 )
Good Luck!
Here's the MEASURE that will do this for you...
Measure = DIVIDE ( SUM ( 'Table'[Value] ), CALCULATE ( COUNTA ( 'Table'[Code] ), ALLEXCEPT ( 'Table', 'Table'[Code] ) ), 0 )
Good Luck!
It will take me a while to understand why this works, but it works. Thank you!
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 |
---|---|
107 | |
93 | |
77 | |
65 | |
53 |
User | Count |
---|---|
147 | |
106 | |
104 | |
87 | |
61 |