Forum Discussion

alialsayer's avatar
alialsayer
Helper I
3 years ago

summarized dax

I just want to calculate how many unique customers have a value per date    1- less than 2$ 2- greater and equal to 2$ and less than 3$ 3- greater and equal to 3$ and less than 4$ 4- above 4$  ...
Need Help
Tutorial Requests
FreemanZ's avatar
FreemanZ
Super User
3 years ago

what do you expect? You have already plotted the expected result with table visual.

  • alialsayer's avatar
    alialsayer
    Helper I
    3 years ago

    To avoid performance issues, I don't want to group it in Table; instead, I want to group it in Dax measurement. 

    • v-yinliw-msft's avatar
      v-yinliw-msft
      Community Support
      3 years ago

      Hi alialsayer,

       

      You can try this method:

      Measure:

      Measure = SUM('Table'[Values($)])
      Measure 2 =
VAR _sum =
    CALCULATE (
        SUM ( 'Table'[Values($)] ),
        ALLEXCEPT ( 'Table', 'Table'[Date], 'Table'[Name] )
    )
RETURN
    SWITCH (
        TRUE (),
        AND ( _sum >= 0, _sum < 2 ), "#DEFFEE",
        AND ( _sum >= 2, _sum < 3 ), "#E044A7",
        AND ( _sum >= 3, _sum < 4 ), "#744EC2",
        _sum >= 4, "#E66C37"
    )

       

      Then in the CONDITIONAL FORMATTING:

       

       

      There are two ways to color them:

      1.

       

      2.

       

      Hope these help you.

      Here is my PBIX file.

       

      Best Regards,

      Community Support Team _Yinliw

      If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

      • alialsayer's avatar
        alialsayer
        Helper I
        3 years ago

        This is fantastic; thank you for your prompt response; however, I have a slightly different situation. First, I have to group values by date for each customer, and then I should use the bucket. I am not interested in color formatting as I will only use the bucket at the end to count how many unique customers are in each bucket matting as I will only use the bucket at the end to count how many unique customers are in each bucket. I am trying to find a way to do this in the DAX function instead of grouping values for each customer in a table to avoid performance issues.

    • FreemanZ's avatar
      FreemanZ
      Super User
      3 years ago

      then what does the 1/2/3/4 grouping mean? What do you expect?

      • alialsayer's avatar
        alialsayer
        Helper I
        3 years ago

        Thank you for your prompt response; however, I have a slightly different situation. First, I have to group values by date for each customer, and then I should use the bucket. I am trying to find a way to do this in the DAX function instead of grouping values for each customer in a table to avoid performance issues.