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.
I am trying to add a column to a table which is:
Average Score * Contract Value / Count of Survey Scores
I have already added a column to find the average score for each customer but am looking for the desired column below.
Customer | Survey Score | Contract Value | Average Score | Desired Column |
A | 10 | 90 | 10 | 900 |
B | 10 | 40 | 10 | 400 |
B | 40 | 10 | 400 | |
C | 120 | 6.7 | 268 | |
C | 120 | 6.7 | 268 | |
C | 7 | 120 | 6.7 | 268 |
C | 5 | 120 | 6.7 | 268 |
C | 8 | 120 | 6.7 | 268 |
I tried the below formula but I was getting errors.
Desired Column = (CALCULATE( AVERAGE('Table'[Contract Value]), ALLEXCEPT('Table','Table'[Customer])) * CALCULATE( AVERAGE('Table'[Contract Value]), ALLEXCEPT('Table','Table'[Customer]))) / CALCULATE( COUNT('Table'[Survey Score]), ALLEXCEPT('Table','Table'[Customer])
I then want to show a card which shows the Sum of each unique customers new value in the desired column. So in this example the card would show 1,568 (900+400+268)
Thanks
Paul
Solved! Go to Solution.
Hi @PaulCo,
Based on my test, you should be able to use the formula below to create the [Desired Column] calculate column in your table.
Desired Column = DIVIDE ( CALCULATE ( AVERAGE ( Table1[Survey Score] ), ALLEXCEPT ( Table1, Table1[Customer] ) ) * Table1[Contract Value], CALCULATE ( COUNT ( Table1[Survey Score] ), ALLEXCEPT ( Table1, Table1[Customer] ) ) )
Then you can use the formula below to create a measure to calculate the Sum of each unique customers new value in the desired column.
Measure = SUMX ( SUMMARIZE ( Table1, Table1[Customer], "Desired Column1", MAX ( Table1[Desired Column] ) ), [Desired Column1] )
Regards
Hi @PaulCo,
Based on my test, you should be able to use the formula below to create the [Desired Column] calculate column in your table.
Desired Column = DIVIDE ( CALCULATE ( AVERAGE ( Table1[Survey Score] ), ALLEXCEPT ( Table1, Table1[Customer] ) ) * Table1[Contract Value], CALCULATE ( COUNT ( Table1[Survey Score] ), ALLEXCEPT ( Table1, Table1[Customer] ) ) )
Then you can use the formula below to create a measure to calculate the Sum of each unique customers new value in the desired column.
Measure = SUMX ( SUMMARIZE ( Table1, Table1[Customer], "Desired Column1", MAX ( Table1[Desired Column] ) ), [Desired Column1] )
Regards
Worked Perfectly, Thanks!
Hi,
Try this calculated field formula
=SUMX(SUMMARIZE('Table',[Customer],"ABCD",MIN('Table'[Average Score])*MIN('Table'[Contract Value])/COUNT('Table'[Survey Score])),[ABCD])
Hope this helps.
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 |
---|---|
114 | |
97 | |
86 | |
70 | |
62 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |