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

v-juanli-msft's avatar
v-juanli-msft
Community Support
7 years ago

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

    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's avatar
      v-juanli-msft
      Community Support
      7 years ago

      Hi lbrown

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

       

      Best Regards

      Maggie

    • v-juanli-msft's avatar
      v-juanli-msft
      Community Support
      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