Dynamic Counting based on value range

[CiccioST]

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

[v-yulgu-msft · Accepted solution]

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]

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]

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]

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