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.
Kia ora
The picture below is a snippet of my data set.
I want to be able to sum the second column based on the data in the first.
I want to sum column 2 for each of the unique sets of data in column 1.
I.e. the sum for 238329 would be 12 and for 238330 would be 12 and so on.
This however is only the start. The actual answer I need is 3 not 12. The second stage is to take the 12 and divide by the count of the 238329's (12/4 in this case).
Hope this is clear. The snip of data above has bben sorted for illustration purposes. The actual data could be in any order and there could be any number of matching data numbers in column 1 rather than the uniform 4 of each show above.
Many thanks in advanc for any help.
Solved! Go to Solution.
Hi AB1977,
Assuming your dataset has a table named "Table1" which first column is named "Column1" and second column is named "Column2" ...
1.- Create a new measure called "Total Column2" with the following DAX code: SUM(Table1[Column2]).
2.- Create a new measure called "Total Column1" with the following DAX code: COUNTA(Table1[Column1]).
3.- Create a new measure called "Total" with the following DAX code: DIVIDE([Total Column2];[Total Column1]).
4.- Create a new visual (table) with Column1 (ID) on rows and each of the measures you've created on columns
That should work
Vicente
Hi @AB1977,
Could you please mark the proper answer as solution or share the solution if it's convenient for you? That will be a big help to the others.
Best Regards!
Dale
Hi AB1977,
Assuming your dataset has a table named "Table1" which first column is named "Column1" and second column is named "Column2" ...
1.- Create a new measure called "Total Column2" with the following DAX code: SUM(Table1[Column2]).
2.- Create a new measure called "Total Column1" with the following DAX code: COUNTA(Table1[Column1]).
3.- Create a new measure called "Total" with the following DAX code: DIVIDE([Total Column2];[Total Column1]).
4.- Create a new visual (table) with Column1 (ID) on rows and each of the measures you've created on columns
That should work
Vicente
Or, since the function described is essentially an average, create a measure AverageC2 = AVERAGE(Table1[Column2]) and create a visual with Column 1 in the first column and this measure in the second.
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 |