Forum Discussion

KarenFingerhut's avatar
KarenFingerhut
Icon for Responsive Resident rankResponsive Resident
4 years ago
Solved

DAX Calculation Issue from granular table to summary level DAX Statement

Hi there

 

Im trying to create a DAX statement but am having problems.

 

The data is at transaction date level giving values of petrol spend per operative. The slicers and dicers at the top are at invoice month level and against each transaction date is the invoice month (if that makes sense)

 

Invoice January 2021, transaction date 4/1/2021, John Smith, Petrol spend 23.41

Invoice January 2021, transaction date 10/1/2021, John Smith, Petrol spend 120.00

Invoice January 2021, transaction date 11/1/2021, John Smith, Petrol spend 10.49

Invoice January 2021, transaction date 21/1/2021, John Smith, Petrol spend 50.00

Invoice January 2021, transaction date 28/1/2021, John Smith, Petrol spend 23.41

 

And so on for all operatives year to date

 

I want to be able to calculate (using the granular data) for each operative, per month, if they have spent more than 300 in total and then create a flag (1,0). I then want to sum up the flag and this will be used in a card visual, which the user of the report can then slice and dice using a month filter or even by the operative name.

 

I created the following DAX statement 

# Flag Spend > 300 Per Month = VAR totalmthtodate = TOTALMTD(CALCULATE(SUM(Fuel[GrossValue])),Fuel[InvoiceDate])
RETURN
CALCULATE(IF(totalmthtodate >300,1,0))

 

The problem Im having is that when summing up it doesnt work in the card visual, it gives completely the wrong value. So I dropped it in to a table visual just to check and the total in the flag column in the table only returns a 1 as well.

 

You can see the total in the card and the total in the table are both wrong, but besied es that there are a lot more than 64 operatives since January 2021 have a spend of >300

 

Any help would be really appreciated

 

Many thanks

Karen

 

Need Help
  • v-janeyg-msft's avatar
    v-janeyg-msft
    4 years ago

    Hi, KarenFingerhut 

     

    I just got this thread today. It's easy to make mistakes with time-intelligence functions so it is not recommended.

    Since you are using slicer, we need to build a context in the card.

    Like this:

    # Flag Spend > 300 Per Month =
COUNTX (
    SUMMARIZE (
        Fuel,
        [InvoiceDate],
        [operative_name],
        "sum", IF ( SUM ( Fuel[GrossValue] ) > 300, 1, 0 )
    ),
    [sum]
)

    Did I answer your question? Please mark my reply as solution. Thank you very much.
    If not, please feel free to ask me.

     

    Best Regards,

    Community Support Team _Janey

     

     

6 Replies

  • KarenFingerhut's avatar
    KarenFingerhut
    Icon for Responsive Resident rankResponsive Resident
    4 years ago

    Hi all

    Just wondering if there is anyone out there that can help with this. Or would you recommend create a summarised tables and doing the caluclation in there

     

    Thank you

    Karen

  • v-janeyg-msft's avatar
    v-janeyg-msft
    Icon for Community Support rankCommunity Support
    4 years ago

    Hi, KarenFingerhut 

     

    I just got this thread today. It's easy to make mistakes with time-intelligence functions so it is not recommended.

    Since you are using slicer, we need to build a context in the card.

    Like this:

    # Flag Spend > 300 Per Month =
COUNTX (
    SUMMARIZE (
        Fuel,
        [InvoiceDate],
        [operative_name],
        "sum", IF ( SUM ( Fuel[GrossValue] ) > 300, 1, 0 )
    ),
    [sum]
)

    Did I answer your question? Please mark my reply as solution. Thank you very much.
    If not, please feel free to ask me.

     

    Best Regards,

    Community Support Team _Janey

     

     

    • KarenFingerhut's avatar
      KarenFingerhut
      Icon for Responsive Resident rankResponsive Resident
      4 years ago

      Janey

      I've learnt something new which will really help me with future dashbaords. Thank you 🙂

      Kind regards

      Karen

      • v-janeyg-msft's avatar
        v-janeyg-msft
        Icon for Community Support rankCommunity Support
        4 years ago

        KarenFingerhut 

         

        Thanks for your recognition, glad you can modify the code yourself, you are smart 🤝.

         

        Best Regards,

        Community Support Team _Janey

         

    • KarenFingerhut's avatar
      KarenFingerhut
      Icon for Responsive Resident rankResponsive Resident
      4 years ago

      Hi Janey

       

      Thanks for getting back to me and apologies for the late reply.

       

      Unfortunately it dont work. The results it was returning were too high when checking against the raw data. I have subsequently come up wth a workaround where I insert another dataset and link them together. However would be good to know how to do this as an additional dataset isnt idea.

      Thank you

      Kind regards

      Karen

    • KarenFingerhut's avatar
      KarenFingerhut
      Icon for Responsive Resident rankResponsive Resident
      4 years ago

      Hi Janey

       

      If I change COUNTX to SUMX it works a treat 🙂

       

      Thank you

      K