CODEWITHSUNDEEP
powerbidaxpowerbi-desktopmeasure
James Holz
James Holz

Reputation: 47

Create measure to calculate rows based on TO and FROM figures in another table

I have a report that fetches weekly data. I have a weeks table that contains the weeks and dates and relationships set up with the below tables.

I have a QueueSummary table; QueueSummary

I have ConciergeHours table;

ConciergeHours

The concierge hours differ from day to day, so i keep the start and finish time in a table so we can update it dynamically.

I am trying to create a measure to calculate how many interactions we have had, between the hours in the ConciergeHours table. Keep in mind this is a weekly report, so on any given day during the week, the hours may be different.

The calculation we are after is;

Number interactions accepted on queue LinkConciergeVQ (QueueSummary table) between the starthour and endhour in ConciergeHours table MINUS Number interactions offered on queue LinkVQ (QueueSummary table) between the starthour and endhour in ConciergeHours table

I currently have the measure looking like this. It works, but the between hours are not respected correctly, which i understand would be a result of the MIN, MAX.

m_deflections = 
CALCULATE (
    SUM ( QueueSummary[s_accepted] ),
    QueueSummary[queue] = "LinkConciergeVQ",
    filter(QueueSummary, [hour] >= min(ConciergeHours[starthour])),
    filter(QueueSummary, [hour] < max(ConciergeHours[endhour]))
)
    - CALCULATE (
        SUM ( QueueSummary[s_offered] ),
        QueueSummary[queue] = "LinkVQ",
        filter(QueueSummary, [hour] >= min(ConciergeHours[starthour])),
        filter(QueueSummary, [hour] < max(ConciergeHours[endhour]))
    )

I hope i've explained this correctly.

If anyone could offer any help!?

Thanks so much

Upvotes: 1

Views: 83

Answers (2)

OscarLar
OscarLar

Reputation: 1335

Not sure I understand your table relationships, but if its 1:* from ConciergeHours to QueueSummary via date then I think you can use this measure, or at least it puts you on the right track, I hope:

m_deflections = 

var s_accept = 
SUMX(
    'ConciergeHours',
    var startH = 'ConciergeHours'[startHour]
    var endH = 'ConciergeHours'[endHour]
    return
    CALCULATE(
        SUM('QueueSummary'[s_accepted]),
        'QueueSummary'[hour] >= startH && 'QueueSummary'[hour] < endH
    )
)

var s_offered = 
SUMX(
    'ConciergeHours',
    var startH = 'ConciergeHours'[startHour]
    var endH = 'ConciergeHours'[endHour]
    return
    CALCULATE(
        SUM('QueueSummary'[s_offered]),
        'QueueSummary'[hour] >= startH && 'QueueSummary'[hour] < endH
    )
)

return 
s_accepted - s_offered

Upvotes: 3

Mike Honey
Mike Honey

Reputation: 15027

I would collapse the 2 pairs of FILTER functions into a single FILTER for each CALCULATE. That will apply the filters against starthour and endhour in combination (the && means AND), where I expect they are currently applying as OR logic so not having any effect.

Along the lines of this:

m_deflections =
CALCULATE (
    SUM ( QueueSummary[s_accepted] ),
    QueueSummary[queue] = "LinkConciergeVQ",
    FILTER (
        QueueSummary,
        [hour] >= MIN ( ConciergeHours[starthour] )
            && [hour] < MAX ( ConciergeHours[endhour] )
    )
)
    - CALCULATE (
        SUM ( QueueSummary[s_offered] ),
        QueueSummary[queue] = "LinkVQ",
        FILTER (
            QueueSummary,
            [hour] >= MIN ( ConciergeHours[starthour] )
                && [hour] < MAX ( ConciergeHours[endhour] )
        )
    )

Upvotes: 1

Related Questions