Measure to calculate set over specified percentage

[Arete]

I have a measure that calculates the percent difference between 2 measures and I want to be able to return all the records that are above 0%

Data: 

Record	Value	Type
1	John Doe	Name
1	123 Main street	Address
2	Jane Doe	Name
2	John Doe	Name

Total Records= 
CALCULATE(
    DISTINCTCOUNT('tbl'[Record])
)    

Total Entities = 
CALCULATE(
    COUNT('tbl'[Value])
)

Total Unique Entities = 
CALCULATE(
    DISTINCTCOUNT('tbl'[Value])
)

Unique Entity % diff = 
DIVIDE([Total Entities] - [Total Unique Entities], [Total Entities])

I want to now have a card show how many records are over 0% but all the measures I create aren't working.

Thanks for the help!

[Shaurya]

Hi @Arete,

I used all your measures and it works fine.

Can you share a screenshot of the issue that you are facing?

[Arete]

Hi @Shaurya!

I'm trying to create a new measure that returns the number of records that are greater than 0 in the Unique Entity % diff measure:

In my data set there can be duplicate values with a different type:

Record	Value	Type
1	John Doe	Person
1	123 Main Street	Address
1	111-11-1111	Social Security Number
1	111-11-1111	Passport Number

In this example, the Total Entities is 4 and the Total Unique Entities is 3 and the % diff is higher than 0% so it should be counted.

[Shaurya · Accepted solution]

Hi @Arete,

You want this difference to be calculated for all records but you are it in a measure which does not support row context. Try this instead:

New Table = SUMMARIZE('Test Table','Test Table'[Record],"Count",COUNT('Test Table'[Value]),"Disctinct Count",DISTINCTCOUNT('Test Table'[Value]))

A new table will be created with count and disctinct count for every record. Add another column in for difference.

Difference = 'New Table'[Count]-'New Table'[Disctinct Count]

Now use this difference column to get the count.

Diff_More_Than_Zero = CALCULATE(COUNT('New Table'[Record]),FILTER('New Table','New Table'[Difference]>0))

You can add more columns based on your need of percentage.

Works for you? Mark this post as a solution if it does!
Check out this blog of mine: How to Export Telemetry Data from Azure IoT Central into Power BI

[Arete]

This is so helpful on so many levels. Thanks for adding the clarification and the quick response. Bravo!