DAX For Weighted Column

[Anonymous]

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. 

[Ashish_Mathur]

Hi,

Your question is not clear.  In a simple Table, please show the expected result on the data that you have shared.

[Anonymous]

@Ashish_Mathur I have added the requested information.

[Ashish_Mathur · Accepted solution]

Hi,

I believe this is the result you want.  You may download my PBI file from here.

Hope this helps.

[Anonymous]

@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)

[Ashish_Mathur]

Hi,

I cannot understand your question.  Share the data and expected result.

[Anonymous]

@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.

[Ashish_Mathur]

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.

[Anonymous]

@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

[Anonymous]

@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?