Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
User | Count |
---|---|
128 | |
108 | |
100 | |
64 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |