cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Tekfes Frequent Visitor
Frequent Visitor

Calculation measure to compare metrics.

I have 2 fact table and 1 dimension table. They are all linked via the dimension table. The dimension table has all policyid in both fact tables. Like so..

 

DimPolicy

-          PolicyID

 

FactTb_1

-          PolicyID

-          Measure_A

-          Measure_B

 

FactTb_2

-          PolicyID

-          Measure_A

-          Measure_B,

 

I want to create calculated measures to compare and identify where measures do not match, or policies do not exist on both fact tables. Please let me know best way to do this.

 

My thinking was , I should create calculated measures on dim table to evaluate measures but I am quite new to power bi and not sure how to do this. Please help.

Desire Outcome

PolicyID, MatchingStatus

1001      Matching

1002      Missing FactTb_1

1003      Not Matching

1004      Missing FactTb_2

2 ACCEPTED SOLUTIONS

Accepted Solutions
Super User
Super User

Re: Calculation measure to compare metrics.

Hello @Tekfes 

The table a measure lives on doesn't really matter so you can write them where they may the most sense.  I will assume for the sake of this discussion that you have a column in both Fact tables called 'Premium'.

Write a measure on each one (again, doesnt matter really but it is easier to see what table you are summing if the measure is under the same table).

Fact1 Premium = SUM ( FactTb_1[Premium] )
Fact2 Premium = SUM ( FactTb_2[Premium] )

Then a thrid measure to do some compares using a SWITCH TRUE()
https://community.powerbi.com/t5/Community-Blog/SWITCH-True-Logic-In-Power-BI-What-s-Possible-With-T...

Compare =
SWITCH (
TRUE(),
ISBLANK ( FactTb_1[PolicyID]  ), "Missing FactTb_1",
ISBLANK ( FactTb_2[PolicyID]  ), "Missing FactTb_2",
[Fact1 Premium] <> [Fact2 Premium], "Not Matching",
[Fact1 Premium] = [Fact2 Premium], "Matching", "Other" )

Pull the policy ID in from your DimPolicy and add the compare measure.  It will return the first TRUE result or "Other" if none of them evaluate true.

View solution in original post

Super User
Super User

Re: Calculation measure to compare metrics.

Hello @Tekfes 

You already have the table you need, you can just add a calculated column to your DimPolicy table, we just need to change the measure a little.

Compare =
SWITCH (
    TRUE (),
    ISBLANK ( COUNTROWS ( RELATEDTABLE ( FactTb_1 ) ) )"Missing FactTb_1",
    ISBLANK ( COUNTROWS ( RELATEDTABLE ( FactTb_2 ) ) )"Missing FactTb_2",
    [Fact1 Premium] <> [Fact2 Premium], "Not Matching",
    [Fact1 Premium] = [Fact2 Premium], "Matching",
    "Other"
)

Compare.jpg

View solution in original post

4 REPLIES 4
Super User
Super User

Re: Calculation measure to compare metrics.

Hello @Tekfes 

The table a measure lives on doesn't really matter so you can write them where they may the most sense.  I will assume for the sake of this discussion that you have a column in both Fact tables called 'Premium'.

Write a measure on each one (again, doesnt matter really but it is easier to see what table you are summing if the measure is under the same table).

Fact1 Premium = SUM ( FactTb_1[Premium] )
Fact2 Premium = SUM ( FactTb_2[Premium] )

Then a thrid measure to do some compares using a SWITCH TRUE()
https://community.powerbi.com/t5/Community-Blog/SWITCH-True-Logic-In-Power-BI-What-s-Possible-With-T...

Compare =
SWITCH (
TRUE(),
ISBLANK ( FactTb_1[PolicyID]  ), "Missing FactTb_1",
ISBLANK ( FactTb_2[PolicyID]  ), "Missing FactTb_2",
[Fact1 Premium] <> [Fact2 Premium], "Not Matching",
[Fact1 Premium] = [Fact2 Premium], "Matching", "Other" )

Pull the policy ID in from your DimPolicy and add the compare measure.  It will return the first TRUE result or "Other" if none of them evaluate true.

View solution in original post

Highlighted
Tekfes Frequent Visitor
Frequent Visitor

Re: Calculation measure to compare metrics.

Thanks Jdbuchanan71.

 

While this works, I realised/think calculation measures will not get me what I eventually want to be able to do.

My end goal is to produce visuals based on “compare” column.

 

For example

 

Reconciliation Report                                     No. Policies                         Premium Ant

Missing FactTb_1                                             10                                                           123

Missing FactTb_2                                             12                                                           300

Matching                                                             10                                                           450

 

Exception Report

List of PolicyId missing from FactTb_1

PolicyID 1

PolicyID 2

 

Do I need to think joining the fact tables and using custom column?

 

Many thanks,

Super User
Super User

Re: Calculation measure to compare metrics.

Hello @Tekfes 

You already have the table you need, you can just add a calculated column to your DimPolicy table, we just need to change the measure a little.

Compare =
SWITCH (
    TRUE (),
    ISBLANK ( COUNTROWS ( RELATEDTABLE ( FactTb_1 ) ) )"Missing FactTb_1",
    ISBLANK ( COUNTROWS ( RELATEDTABLE ( FactTb_2 ) ) )"Missing FactTb_2",
    [Fact1 Premium] <> [Fact2 Premium], "Not Matching",
    [Fact1 Premium] = [Fact2 Premium], "Matching",
    "Other"
)

Compare.jpg

View solution in original post

Tekfes Frequent Visitor
Frequent Visitor

Re: Calculation measure to compare metrics.

Thanks you!

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 122 members 1,568 guests
Please welcome our newest community members: