Forum Discussion

krichmond's avatar
krichmond
Icon for Helper IV rankHelper IV
3 years ago

DAX Formula Not Returning Expected Results. Need Some Help Debugging.

I have a formula field where it isn't returning the expected results and I need help debugging it. The field leverages two other fields. One of the fields is called "Marketing Quantity" and it has hard coded values in it. The other field is called "Actual Premium With Time Designator" and it has a formula in it (below). Basically all this does is pull premium from different fields based on where the marketing campaign is on the response curve. Both the "Marketing Quantity" and "Actual Premium With Time Designator" fields are working as expected. I need to now create a formula field called "Premium Per Piece Mailed" that takes the "Actual Premium With Time Designator" field and divides it by the "Marketing Quantity" field. I tried this using this formula: 

PPPM (Calculated) = 'This Week'[Actual Premium With Time Designator] / 'This Week'[Marketing Quantity] but for some reason the results are coming through as expected. For January the PPPM should be $0.58 but it is showing up as $15.79. For February the PPPM should be $1.00 but it is showing up as $278.24. Any help you can offer would be greatly appreciated.
 

 

Actual Premium With Time Designator

Actual Premium With Time Designator = SWITCH(TRUE(),
'This Week'[90 Day Date Designator]="True",'This Week'[Projected Average Buy]*'This Week'[Projected Paid Responders],
'This Week'[180 Day Date Designator]="True",'This Week'[Premium @100%],
'This Week'[Is After Today]="True",'This Week'[Projected Premium],
'This Week'[180 Day Date Designator]="False",'This Week'[Actual Premium])
Need Help

2 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    3 years ago

    krichmond , if these are columns, create a measure like

     

    PPPM (Calculated) = 'divide(Sum(This Week'[Actual Premium With Time Designator] ),Sum( 'This Week'[Marketing Quantity]))

     

    If this does not help
    Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

    • krichmond's avatar
      krichmond
      Icon for Helper IV rankHelper IV
      3 years ago

      amitchandak - That didn't work but I am now wondering if it has something to do with the filters. Will a DAX formula automatically adjust if the chart has filters applied to it? Or will I need to embed the filter criteria into the formula itself as well? On that chart I have three filters applied.