Forum Discussion

CiccioST's avatar
CiccioST
New Member
8 years ago
Solved

Dynamic Counting based on value range

Dears, I am a new  POWER BI user and I am trying to figure the best way to do things currently done with Pivot tables and excel in general. only basic knowledge of DAX language.   I have a huge a...
Need Help
  • v-yulgu-msft's avatar
    v-yulgu-msft
    8 years ago

    Hi CiccioST,

     

    Add a calculated column in data table.

    buying category =
IF (
    CALCULATE (
        SUM ( 'Customer purchase'[Value] ),
        ALLEXCEPT (
            'Customer purchase',
            'Customer purchase'[Customer Name],
            'Customer purchase'[Month]
        )
    )
        < 300,
    "Buying<300",
    "Buying>300"
)

    Use a Matrix to display data, add [Customer Name] to "values" section, choose "Count(distinct)".

    Best regards,

    Yuliana Gu

v-yulgu-msft's avatar
v-yulgu-msft
Microsoft Employee
8 years ago

Hi CiccioST,

 

Add a calculated column in data table.

buying category =
IF (
    CALCULATE (
        SUM ( 'Customer purchase'[Value] ),
        ALLEXCEPT (
            'Customer purchase',
            'Customer purchase'[Customer Name],
            'Customer purchase'[Month]
        )
    )
        < 300,
    "Buying<300",
    "Buying>300"
)

Use a Matrix to display data, add [Customer Name] to "values" section, choose "Count(distinct)".

Best regards,

Yuliana Gu

  • CiccioST's avatar
    CiccioST
    New Member
    8 years ago

    HI Yuliana,

     

    Just saw your reply on my post, thanks for your time and suggestion!

    I will try this formula soon and keep you posted.

     

    Cheers

    FR

    • CiccioST's avatar
      CiccioST
      New Member
      8 years ago

      HI Yuliana,

       

      The solution you proposed worked perfectly for me!

      I can now change dynalically all filters in my dashboard and get the data sorted by category.

      I have a very silly question now: If I want to add more categories into the value range, should I just copy and paste the formula starting from IF and just changing the range I want to get?

       

      Thanks a lot!!

      FR

      • CiccioST's avatar
        CiccioST
        New Member
        8 years ago

        I got it! It works by copying the whole formula from IF and changing the range I want to get.

        Excellent!

        Thanks a lot for your super support.

         

        FR