Calculate daily rate based on fields from different fact tables

[Phil2ps]

Hi all,

I have 2 fact tables storing Open and Sent information. Using these tables I can display how many messages were sent/opened on a specific date.

There is also a separate dimension table storing date information. The date dimension is joined to the 2 fact tables on date fields. I use the date field from the dimension table as a slicer to filter and/or display the data over time.

fact_Sent

Date	CountSent	Message_id
01/01/2023	100	A
01/01/2023	200	B

fact_Open

Date	CountOpen	Message_id
01/01/2023	50	A
02/01/2023	20	A
01/01/2023	40	B

I can built a simple visual showing:

Message_id	Date	CountSent	CountOpen
A	01/01/2023	100	50
A	02/01/2023	NULL	20
B	01/01/2023	200	40

As per above table, a message is sent on a specific day but the open can occur over multiple days.

Now I wanted to calculate the open rate ( CountOpen/CountSent) but I'm getting INFINITY values for days where CountSent is NULL.

I understand I should be able to create a measure to calculate the open rate but I'm struggling to build its logic. Essentially, the logic should be 'on date X, divide 'CountOpen from date X) by CountSent'.

The results I'm trying to get is:

Message_id	Date	CountSent	CountOpen	OpenRate
A	01/01/2023	100	50	50%
A	02/01/2023	NULL	20	20%
B	01/01/2023	200	40	20%

Any suggestion?

Thanks. 

[Anonymous · Accepted solution]

Hi @Phil2ps 

You can refer to the following soltion.

1.Create a type table of message_id 

2.Create the relationship between the tables

3.Create the following measures

Sum_open = SUM(fact_Open[CountOpen]) 

Sum_sent = SUM(fact_Sent[CountSent])

Divide_ =
VAR _predate =
    IF (
        OR ( [Sum_open] <> BLANK (), [Sum_sent] <> BLANK () ),
        MAXX (
            FILTER (
                ALLSELECTED ( fact_Sent ),
                [Message_id]
                    IN VALUES ( 'Type'[Message_id] )
                        && [Date] < SELECTEDVALUE ( 'Date'[Date] )
                        && [CountSent] <> BLANK ()
            ),
            [Date]
        )
    )
VAR b =
    MAXX (
        FILTER (
            ALLSELECTED ( fact_Sent ),
            [Message_id]
                IN VALUES ( 'Type'[Message_id] )
                    && [Date] = _predate
        ),
        [CountSent]
    )
RETURN
    IF (
        [Sum_sent] <> BLANK (),
        DIVIDE ( [Sum_open], [Sum_sent] ),
        DIVIDE ( [Sum_open], b )
    )

Output

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[mickey64]

For your reference.

Step 1: I add a relationship.

Step 2: I make a measure.

    Open % = DIVIDE(SUM('fact_Open'[CountOpen]),SUM('fact_Sent'[CountSent]))

Step 3: I make a 'Table' visual.