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

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