Forum Discussion
Help with summing alternative columns
Hello everyone,
Thanks in advance for your help. The last row is the formula that needed to be calculated. Any help is apprecaited.
| Neg | Neg Percent Of Total Tested | Pending | Pending Percent Of Total Tested | Pos | Pos Percent of Total Tested | Pos Percent Of Known Results | Total tested | |
| Test 1 | 2334 | 49.02 | 165 | 3.47 | 2262 | 47.51 | 49.22 | 4761 |
| Test 2 | 228 | 47.01 | 16 | 3.3 | 241 | 49.69 | 51.39 | 485 |
| Total | 2562 (B7) | 48.84 | 181 (D7) | 3.45 | 2503 (F7) | 47.71 | 49.42 | 5246 (I7) |
| SUM | Cell B7/I7 (2562/5246) | SUM | Cell D7/I7 (181/5246) | SUM | Cell F7/I7 (2503/5246) | Cell F7/(F7+B7) (2503/2503+2562) | SUM |
hi nanchil
For SUM calculation, you could just drag the field into visual directly,
for Percent measure, you could create this measure as below:
Measure Neg Percent Of Total Tested = DIVIDE ( CALCULATE ( SUM ( 'Table'[Neg] ) ), CALCULATE ( SUM ( 'Table'[Total tested] ) ) )Measure Pending Percent Of Total Tested = DIVIDE ( CALCULATE ( SUM ( 'Table'[Pending] ) ), CALCULATE ( SUM ( 'Table'[Total tested] ) ) )Measure Pos Percent Of Known Results = DIVIDE ( CALCULATE ( SUM ( 'Table'[Pos] ) ), CALCULATE ( SUM ( 'Table'[Pos] ) )+CALCULATE(SUM('Table'[Neg])) )Measure Pos Percent of Total Tested = DIVIDE ( CALCULATE ( SUM ( 'Table'[Pos] ) ), CALCULATE ( SUM ( 'Table'[Total tested] ) ) )Result:
and here is sample pbix file, please try it.
Regards,
Lin
4 Replies
nanchil , we are not seeing excel cells here, so not very clear.
Can you share sample data and sample output.
nanchil Read this post to get your answer quickly.
https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490Thanks ! the first two rows are the sample data. The third row is the total that is calculated. The fourth row is the formula that is used to arrive the third row,.
hi nanchil
For SUM calculation, you could just drag the field into visual directly,
for Percent measure, you could create this measure as below:
Measure Neg Percent Of Total Tested = DIVIDE ( CALCULATE ( SUM ( 'Table'[Neg] ) ), CALCULATE ( SUM ( 'Table'[Total tested] ) ) )Measure Pending Percent Of Total Tested = DIVIDE ( CALCULATE ( SUM ( 'Table'[Pending] ) ), CALCULATE ( SUM ( 'Table'[Total tested] ) ) )Measure Pos Percent Of Known Results = DIVIDE ( CALCULATE ( SUM ( 'Table'[Pos] ) ), CALCULATE ( SUM ( 'Table'[Pos] ) )+CALCULATE(SUM('Table'[Neg])) )Measure Pos Percent of Total Tested = DIVIDE ( CALCULATE ( SUM ( 'Table'[Pos] ) ), CALCULATE ( SUM ( 'Table'[Total tested] ) ) )Result:
and here is sample pbix file, please try it.
Regards,
Lin
