Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
AdamO
Frequent Visitor

Actual Achieved vs. Monthly target

Hi,

 

I'm trying to make a report which shows how many sites have been visited per time period (not actually a month, but close enough), vs the target for that period.

 

I have a table which shows the frequency target, and a second table which shows all the individual site visit data. What I'm having difficulty is linking the two together to see how many total visits have been made in a period. I've managed to set my coverage so I can tell whether a site has been visited at all, but not to the correct frequency.

 

Below is an example of the data I have:

 

tables.png

 

So for P1, Site 1, I want a Frequency Actual value of 3; for P1, Site 2, a Frequency Actual value of 2; for P1, Site 3, a Frequency Actual value of 1 and so on.

 

Any suggestions?

 

Thanks!

1 ACCEPTED SOLUTION
Zubair_Muhammad
Community Champion
Community Champion

@AdamO

 

Try this Calculated Column

 

Frequency Actual =
CALCULATE (
    COUNTROWS ( VisitData ),
    FILTER (
        VisitData,
        VisitData[Site Code] = Targets[Site Code]
            && VisitData[Visit Period] = Targets[Period]
    )
)

Regards
Zubair

Please try my custom visuals

View solution in original post

2 REPLIES 2
Zubair_Muhammad
Community Champion
Community Champion

@AdamO

 

Try this Calculated Column

 

Frequency Actual =
CALCULATE (
    COUNTROWS ( VisitData ),
    FILTER (
        VisitData,
        VisitData[Site Code] = Targets[Site Code]
            && VisitData[Visit Period] = Targets[Period]
    )
)

Regards
Zubair

Please try my custom visuals

Fantastic, thanks!  I'd tried a lot of different filter types, but the double amphersand was the step I was missing.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.