Forum Discussion

Karlie69's avatar
Karlie69
Regular Visitor
5 years ago
Solved

Calculation the quantity for non-existent data in the table

Hi 🙂 The problem I have is as follows: I have a table that stores the audit results. The audit is carried out once or twice a month. Sometimes the audit should be carried out twice, but it was n...
Need Help
  • v-kelly-msft's avatar
    v-kelly-msft
    5 years ago

    Hi Karlie69 ,

     

    You should also have an audit table containing all the audit data,right?

    Suppose that you have a table like below:

    Area Expected performed times
    Administration 3
    a 2
    b 1
    c 2
    d 1

    Then create a calculated column as below：

     

    Count of performed audits = LOOKUPVALUE('Table'[Count of performed audits],'Table'[Area],'Table (2)'[Area],0)

     

    And you will see:

    For my sample .pbix file,pls see attached.

     

    Best Regards,
    Kelly

    Did I answer your question? Mark my post as a solution!

v-kelly-msft's avatar
v-kelly-msft
Icon for Community Support rankCommunity Support
5 years ago

Hi Karlie69 ,

 

You should also have an audit table containing all the audit data,right?

Suppose that you have a table like below:

Area Expected performed times
Administration 3
a 2
b 1
c 2
d 1

Then create a calculated column as below：

 

Count of performed audits = LOOKUPVALUE('Table'[Count of performed audits],'Table'[Area],'Table (2)'[Area],0)

 

And you will see:

For my sample .pbix file,pls see attached.

 

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!