Forum Discussion
Convert Tableau calculations into PowerBi dax
Hi friends! Please help!
I'm new to PowerBI and I need to convert a couple of calculation from Tableau into PowerBI:
- I need to calculate the total number of fields that were marked with an error for a specific user.
- I need to calculate the total number of fields reviewed for a specific user.
- I need to calculate the above two but cross all users.
- I need to visualize top 4 fields with errors across all users.
Context: users work/update fields. The reviewers check those worked fields and mark with Error or No Error. We need to calculate the defect ratio between the fields marked with errors versus total fields reviewed for each user. Also, need to calculate the defect ratio overall across all users.
Thanks so much for all your help!!!!
In Tableau the calculations look like:
Defect ratio: 1-(SUM([No Error Count])/SUM([All Field Count]))
No Error Count: {FIXED [User],[ReviewDate], [ErrorType], [ReviewItemID]:SUM(IF [ErrorType]="No Error" THEN 0 Else 1 END)}
All Field Count: {FIXED [User],[ReviewDate], [ReviewItemID]:COUNT([ErrorType])}
I tried putting dax together but didn't get any meaningful results.
Sample data looks like the 3rd table below. So, for example, then I would calculate the Correctness indicator for June, which is =(100-(7*100/28))/100 (Incorrect Info errors=7, all reviewed fields=28). I would also calculate the Completeness indicator for June, which is =(100-(5*100/28))/100 (Missing errors=5, all reviewed fields=28).
Top 4 fields with errors across all users:
| Document Content | 6 |
| Document Remarks | 7 |
| Document Title | 8 |
| Document Translation | 7 |
| User | Assessment Score | Calculation explanation |
| User 1 | 55.56% | =(100-(4*100/9))/100 |
| User 2 | 50.00% | =(100-(5*100/10))/100 |
| User 3 | 66.67% | =(100-(3*100/9))/100 |
| User | ItemID | Field | ErrorType | Status | Review Date |
| User 1 | 12345 | Document Title | Incorrect Information | Correction Needed | Jun-24 |
| 12345 | Document Content | No Error | No Correction Needed | Jun-24 | |
| 12345 | Document Translation | No Error | No Correction Needed | Jun-24 | |
| 12345 | Document Remarks | No Error | No Correction Needed | Jun-24 | |
| User 1 | 123456 | Document Title | Missing | Correction Needed | Jun-24 |
| 123456 | Document Title | Incorrect Information | Correction Needed | ||
| 123456 | Document Content | Missing | Correction Needed | Jun-24 | |
| 123456 | Document Translation | No Error | No Correction Needed | Jun-24 | |
| 123456 | Document Remarks | No Error | No Correction Needed | Jun-24 | |
| User 2 | 67890 | Document Title | No Error | No Correction Needed | Jun-24 |
| 67890 | Document Content | No Error | No Correction Needed | Jun-24 | |
| 67890 | Document Translation | Incorrect Information | Correction Needed | Jun-24 | |
| 67890 | Document Remarks | Missing | Correction Needed | Jun-24 | |
| 67890 | Document Remarks | Incorrect Information | Correction Needed | Jun-24 | |
| User 2 | 9876 | Document Title | Incorrect Information | Correction Needed | Jun-24 |
| 9876 | Document Title | Missing | Correction Needed | Jun-24 | |
| 9876 | Document Content | No Error | No Correction Needed | Jun-24 | |
| 9876 | Document Translation | No Error | No Correction Needed | Jun-24 | |
| 9876 | Document Remarks | No Error | No Correction Needed | Jun-24 | |
| User 3 | 1234567 | Document Title | Incorrect Information | Correction Needed | Jun-24 |
| 1234567 | Document Content | No Error | No Correction Needed | Jun-24 | |
| 1234567 | Document Translation | Missing | Correction Needed | Jun-24 | |
| 1234567 | Document Translation | Incorrect Information | Correction Needed | Jun-24 | |
| 1234567 | Document Remarks | No Error | No Correction Needed | Jun-24 | |
| User 3 | 654321 | Document Title | No Error | No Correction Needed | Jun-24 |
| 654321 | Document Content | No Error | No Correction Needed | Jun-24 | |
| 654321 | Document Translation | No Error | No Correction Needed | Jun-24 | |
| 654321 | Document Remarks | No Error | No Correction Needed | Jun-24 |
AMorulita First, I would use the Fill down feature in Power Query to fill out your User column. Then you can do things like this:
Measure 1 = VAR __Table = SUMMARIZE( FILTER( 'Table', [ErrorType] <> "No Error" && [User] = "User 1" ), [ItemID], [Field] ) VAR __Result = COUNTROWS( __Table ) RETURN __Result Measure 2 = COUNTROWS( FILTER( 'Table', [User] = "User 1" ) ) Measure 3a = VAR __Table = SUMMARIZE( FILTER( 'Table', [ErrorType] <> "No Error" ), [ItemID], [Field] ) VAR __Result = COUNTROWS( __Table ) RETURN __Result Measure 3a = COUNTROWS( 'Table' )For the last one you can use a TopN filter or TOPN DAX function or perhaps RANKX
1 Reply
AMorulita First, I would use the Fill down feature in Power Query to fill out your User column. Then you can do things like this:
Measure 1 = VAR __Table = SUMMARIZE( FILTER( 'Table', [ErrorType] <> "No Error" && [User] = "User 1" ), [ItemID], [Field] ) VAR __Result = COUNTROWS( __Table ) RETURN __Result Measure 2 = COUNTROWS( FILTER( 'Table', [User] = "User 1" ) ) Measure 3a = VAR __Table = SUMMARIZE( FILTER( 'Table', [ErrorType] <> "No Error" ), [ItemID], [Field] ) VAR __Result = COUNTROWS( __Table ) RETURN __Result Measure 3a = COUNTROWS( 'Table' )For the last one you can use a TopN filter or TOPN DAX function or perhaps RANKX