Lookup value in Matrix

[Anonymous]

Hi everyone,

I have a problem to do calculation by using Matrix. 

Column: Reporting Date (i.e. March 2, 2020 -- 2)

Row: All rows are calculated from Tables

Table 1: Claim Info. - One claim one line(including Reporting date, Examiner name, Claim #,Claim amount etc.)

Table 2: Daliy Target Lookup 

Currently, my calculation is to create one column in Table 1 ---- Assign Target to each claim (each line) based on Date and Examiner name. Then, sum target for each line and divided by number of reporting days.

This menthod is good if the target for each day is the same. But, I have problem to calculate the total target for the month if the daily targets are not the same in different days. 

I am thinking if I can assign daliy target to each reporting day with claims processed directly in Matrix, instead to each line in Table 1, all issues could be solved. But, I dont know how to do the calculation. Could you please help me? Or, anyone may have better idea?

Thanks in advance

[v-gizhi-msft · Accepted solution]

Hi,

According to your description, i create a sample to test:

Then please try this measure:

Measure = 
VAR CountOfPerDay =
    CALCULATE (
        COUNT ( 'Table 1'[Reporting Date] ),
        FILTER (
            ALLSELECTED ( 'Table 1' ),
            'Table 1'[Examiner name] IN FILTERS ( 'Table 1'[Examiner name] )
                && 'Table 1'[Reporting date] IN FILTERS ( 'Table 1'[Reporting date] )
        )
    )
RETURN
    CALCULATE (
        SUM ( 'Table 2'[Target] ),
        FILTER (
            ALLSELECTED ( 'Table 2' ),
            'Table 2'[Examiner] IN FILTERS ( 'Table 1'[Examiner name] )
                && 'Table 2'[Reporting Date] = MAX ( 'Table 1'[Reporting date] )
        )
    ) / CountOfPerDay

The result shows the assigned target value for each line per day:

Here is my test pbix file:

pbix 

Hope this helps.

Best Regards,

Giotto Zhi

[amitchandak]

@Anonymous 

You can create common dimensions/tables like examiner and date and join  them you both tables and use those two to analyze data

[Greg_Deckler]

Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490