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!

amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
5 years ago

Karlie69 , Join if with date table, if you do not have date create one using month year.

 

Now create a measure like this and plot it with month year from date table 

 

if(isblank(count(audit[audit])),1, blank())