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

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

      • lbrown's avatar
        lbrown
        Helper I
        7 years ago

        Anonymous

         

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