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 amount of data to treat monthly for internal analysis, and I am trying to use POwer BI to optimize hours of manual job everytime I need to update something.

 

I made the below example to explain what I wish I could do.

 

I have my table on the left side with customers buying every month different amounts for different products.

I made a simple table on the left where customer transactions are repeated as they buy different products (I did not put the products in the table)

 

What I wish I could build in POWER BI, is a similar table (or visual graph) as the one on the right (see pic) where I could have

for each month, the  customers counting split by value range.

 

The "customer counting" need to return the N# of distinct customer where their total in that given month is included in the fixed range.

Ideally, if I put the year or the month in a slicer or fiter, I could select the month I want and get the custmer counting (distinct) based on their total value in that given month.

 

I am not sure I well explained the wished result, but hope I can get some help.

 

Thanks in advance for anyone support.

 

 

 

Ciccio

 

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

4 Replies

  • 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