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

 

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