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 a...
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

     

     

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

Hi Janey

 

If I change COUNTX to SUMX it works a treat 🙂

 

Thank you

K