Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Lookup value in Matrix

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

 

Need Help
  • v-gizhi-msft's avatar
    v-gizhi-msft
    6 years ago

    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

     

3 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

    Anonymous 

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

     

  • v-gizhi-msft's avatar
    v-gizhi-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    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