Need Help With Card Visual

[lbrown]

I'm trying to make a Card visual of the total commissions amount. My problem is that the commission amount is
based on the account number which the account number can be on multiple rows. So I need the measure to add the total
per account number. I currently have a MaxDate Measure which keeps the most recent account number in the visual table, but this date needs to be accounted for in the Card visual as well. Right now the Total Commissions is giving me the total in the commissions column in the data table, not the total from the visual tables in the screenshot, which is what I need. So the Total Commissions should show $5,713.

I have taken out the account numbers in the screenshot since these are confidential to the company.

[v-juanli-msft]

Hi @lbrown

Could you show some an dataset example?

As i analyzed, the Total Commissions should show $5,713=21+22+362+5308, so what is the relationship between 

Total Commissions and Correct Distinct Count?

Without knowing your dataset, i make a test to let you know how to get the total based on the max date.

Measure = var maxdate=CALCULATE(MAX([date]),ALL(Sheet6)) return CALCULATE(DISTINCTCOUNT(Sheet6[Id]),FILTER(Sheet6,[date]=maxdate))

Best Regards

Maggie

[lbrown]

@v-juanli-msft

For some reason I am getting an error message around the return part of the measure shown in the screenshot.

The total commission measure is a sum of the commission payout column from the data. While the Correct Distinct Count is a sumx of the distinct commission payout column. 

[v-juanli-msft · Accepted solution]

Hi @lbrown

Your formula need to modify, please see the bold character which is different from yours

Commission Measure =
VAR maxdate =
    CALCULATE (
        MAX ( 'Retained &Winback Call Logs'[Create Timetamp] ),
        ALL ( 'Retained &Winback Call Logs' )
    )
RETURN
    CALCULATE (
        DISTINCTCOUNT ( 'Retained &Winback Call Logs'[ESG Account Number] ),
        FILTER (
            'Retained &Winback Call Logs',
            'Retained &Winback Call Logs'[Timestamp] = maxdate
        )
    )

From your formula, you use [MaxDate Measure] instead of "maxdate", please modify.

Best Regards

Maggie

[v-juanli-msft]

Hi @lbrown

You need to filter out the "," from your formula.

Best Regards

Maggie

[Anonymous]

Can you do something like ...

DailyCommission = calculate([Commission],FILTER('Name of your Calendar Table','Calendar Table'[Date]=Today()..

You might need to make Today()-1, assuming your data feed comes through every midnight / morning...

Make sure you label your comission card to say "Last Days Comissions" to be clear...

[lbrown]

@Anonymous

This unfortunately didn't work. This gives me a blank when I add the field to the Card Visual. 

[Anonymous]

My inclination is that today() isn't able to match up with your Create Timestamp.

I could be wrong...but I see your date logic is hooked up straight to (what looks like) your raw data file, likely with the column header of: "Create Timestamp".

If there's any chance of extra Calendar-based metric reporting that you might need to show, a lot of the tutorials suggest you use a Calendar table to power all time-based calculations (MTD, QTD, YTD, etc.)

Here's a fast one: https://www.youtube.com/watch?v=F7kc4pd6TVU


Edit:

What format is your Create Timestamp formatted to? Date/Time? etc.?

[lbrown]

@Anonymous

The format of the Create Timestamp is Date. I also added a Date Table to use in the formula, but I'm still getting the complete total of the column when all the dates are considered. I think the Total Commissions has some effect on the formula. Right now the Total Commissions = sum([Commissions Payout]) which is considering the sum of the total commissions column without considering the max date measure.

[Anonymous]

Looks like you've already got Step 1 done.
Total Commissions $ = sum('Retained & Winback Call Logs'[Comission Payout])

Step 2 Try something like this?

Daily Commission $ = CALCULATE([Total Comissions $], DATESBETWEEN('Create Date'[Call Log Date],TODAY()-1,TODAY())

[lbrown]

@Anonymous

For this measure I am still seeing the total commission amount as a sum of the column.