Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello, I am looking to create a weighted calculation of data quality in the organization.
The data consits of the following columns:
Application
Data Quality Attribute
Data Subject Area
Business Usage
Violation Record ID
Violation Source Count
Source Column
Source Column Weight
The formula for Weighted Violations
SUM(For every column calculate the source column weight, * distinct count of violation record id)
Expected Output: Numerator
Filters: Everything is set to all
Column | Column Weight | Distinct Count of Entities | Product of Weight * Entities | |
A | 8 | 3 | 24 | |
B | 8 | 2 | 16 | |
C | 2 | 2 | 4 | |
D | 4 | 2 | 8 | |
E | 8 | 2 | 16 | |
F | 1 | 1 | 1 | |
G | 2 | 1 | 2 | |
H | 1 | 1 | 1 | |
J | 1 | 1 | 1 | |
73 | Expected Output |
Filters: ScoreCard A is chosen
Column | Column Weight | Distinct Count of Entities | Product of Weight * Entities | |
A | 8 | 3 | 24 | |
C | 2 | 2 | 4 | |
D | 4 | 2 | 8 | |
F | 1 | 1 | 1 | |
H | 1 | 1 | 1 | |
38 | Expected Output |
I have attached a link of power bi file and dummy data.
https://www.dropbox.com/s/zids3ejrkkijb6i/attachments.zip?dl=0
This needs to work when select all is selected.
Solved! Go to Solution.
Hi,
I believe this is the result you want. You may download my PBI file from here.
Hope this helps.
Hi,
Your question is not clear. In a simple Table, please show the expected result on the data that you have shared.
Hi,
I believe this is the result you want. You may download my PBI file from here.
Hope this helps.
@Ashish_Mathur Can you answer another query for me.
If i wanted to calculate the sum of source keys for all data subject areas. How would that be done? This needs to be filterable by Application only.
Account 100000
Customer 5000
Total = 105000 (Expected Ans)
Hi,
I cannot understand your question. Share the data and expected result.
@Ashish_Mathur If you look in the same data set used in your solution, every row has a Source Key Count attatched to it. For Account the value is 100K and for Customer it is 5K. I need the total to be 105K and it need to be filterable by Application column.
To simplify I am looking for the total number of sourcekeys per application. The issue is we have many rows per Data Subject Area(Account & Customer) per application.
Hi,
Sorry for the delay in replying. I just saw your post. I downloaded the file but am unable to understand your expected result. Please let me know the exact answer/output you are expecting. Thank you.
@Ashish_Mathur I added this as a seperate problem to avoid confusion, can you please have a look at it.
https://community.powerbi.com/t5/Desktop/Sum-of-Distinct-Values/m-p/539037#M253079
@Ashish_Mathur Let me try this again.
This is a data quality dashboard. Where I have a Source Column Dimension. Each source column belongs to a data subject area i.e. Account, Customer etc.
When I am looking at Data Subject Area for a particular CRM(Application) i.e. App1, App2. I have the total number of customers, and total number of accounts in the Source Entity Key Column. In this case those values are 5K and 100K respectively.
I want to calculate the total customer % as a % of the total records. i.e. 5000/(5000+100000) and 100000/(5000+100000). These calculations need to filterable by the Application and Data Subject Area Column.
Is this the info you need?
User | Count |
---|---|
93 | |
84 | |
78 | |
75 | |
66 |
User | Count |
---|---|
115 | |
105 | |
93 | |
65 | |
60 |