Forum Discussion
incremental row level % with two calculated columns
Hi!
I am looking to divide number of Incorrect checks
( defined as calculated column:
Incorrect Spotchecks = if('Spotchecks doc customs merged dim parcels'[Price OK]="No" || 'Spotchecks doc customs merged dim parcels'[Quantity OK]="No" || 'Spotchecks doc customs merged dim parcels'[Description OK]="No" || 'Spotchecks doc customs merged dim parcels'[Counterfeit]="Yes" || 'Spotchecks doc customs merged dim parcels'[B2B]="Yes", 1, 0)
by Total checks (defined as calculated column:
Total Spotchecks = if(NOT(ISBLANK('Spotchecks doc customs merged dim parcels'[Index])),1,0)
In such way that not only the Total % value is correct, but such that if I add a date column, the daily % values are also correct, which is not the case currently.
Using quick measure Division function creates:
Count of Incorrect Spotchecks divided by Count of Total Spotchecks = DIVIDE( COUNTA('Spotchecks doc customs merged dim parcels'[Incorrect Spotchecks]), COUNTA('Spotchecks doc customs merged dim parcels'[Total Spotchecks]) )
But that results in each row output of 100% which is incorrect (Incorrect checks =25, total checks =35 so I should get 71% total
Is division by calculated columns any different than regular ones? And how can I obtain the division that results in accurate row level % values? Thanks!
3 Replies
Please provide sanitized sample data that fully covers your issue. If you paste the data into a table in your post or use one of the file services it will be easier to assist you. I cannot use screenshots of your source data.
Please show the expected outcome based on the sample data you provided. Screenshots of the expected outcome are ok.
https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523- Count of Incorrect Spotchecks divided by Count of Total Spotchecks =DIVIDE(COUNTA('Spotchecks doc customs merged dim parcels'[Incorrect Spotchecks]),COUNTA('Spotchecks doc customs merged dim parcels'[Total Spotchecks]))The last column should have given a result of diving the incorrect spotchecks by the total spotchecks on each row, so 1/4 for first date, 2/4 for the next one, etc. BIBen is there anything else you need?
It is solved
