Forum Discussion

lbrown's avatar
lbrown
Helper I
7 years ago
Solved

Need Help With Card Visual

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 ...
General Comment
Need Help
  • v-juanli-msft's avatar
    v-juanli-msft
    7 years ago

    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

Anonymous's avatar
Anonymous
Not applicable
7 years ago

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's avatar
lbrown
Helper I
7 years ago

Anonymous

 

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    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's avatar
      lbrown
      Helper I
      7 years ago

      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's avatar
        Anonymous
        Not applicable
        7 years ago

        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())