Issue with a table that compares targets with real sales

[Tocosta]

Hi!

I have a table that have all the information about the targets of each Salesperson. The information display is Nº of Salesperson, month of the target and quantity of target.

Then on another table where I have all the sales each Salesperson do, i made a measure that has the sum of the sales each one made on each month.

I want to make a table on power BI that compares this measure with the target.

simmilar to this one.

this should have a filter where I can introduce the month so I can see only the month I selected.

Can anyone please help me?

thanks!!

[amitchandak · Accepted solution]

@Tocosta , If you have date, otherwise create date with month and year

date = "01-" & [month] &"-" &[year] 

Also create common date for sales person - Bridge Table: https://www.youtube.com/watch?v=Bkf35Roman8&list=PLPaNVDMhUXGaaqV92SBD5X2hk3TMNlHhb&index=19

Join these two table with Common date and common sales person tables and analyze with those 

% = divide(sum(Target[Target]), sum(Sales[Sales]))

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

[v-yalanwu-msft · Accepted solution]

Hi @Tocosta , 

You could create a measure by the following formula:

_Sales =
IF (
    ISFILTERED ( Table1[Month] ),
    SUMX ( FILTER ( 'Table2', [Month] = SELECTEDVALUE ( Table1[Month] ) ), [Sales] ),
    SUM ( Table2[Sales] ))

And

compare% =
IF (
    ISFILTERED ( Table1[Month] ),
    DIVIDE (
        SUM ( 'Table1'[Target] ),
        SUMX ( FILTER ( 'Table2', [Month] = SELECTEDVALUE ( Table1[Month] ) ), [Sales] )),
    DIVIDE ( SUM ( 'Table1'[Target] ), SUM ( Table2[Sales] ) ))

Then add a filter to select month:

The final output is shown below:

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.  

[amitchandak · Accepted solution]

@Tocosta , If you have date, otherwise create date with month and year

date = "01-" & [month] &"-" &[year] 

Also create common date for sales person - Bridge Table: https://www.youtube.com/watch?v=Bkf35Roman8&list=PLPaNVDMhUXGaaqV92SBD5X2hk3TMNlHhb&index=19

Join these two table with Common date and common sales person tables and analyze with those 

% = divide(sum(Target[Target]), sum(Sales[Sales]))

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.