Forum Discussion

polman4's avatar
polman4
Helper I
3 years ago
Solved

Distinctcount values by custom filter

Hello,   I have a table (it's created with append) where I want to count how many products sold between specific total revenues by year. For example some random numbers: - less than 10000$, - bet...
Need Help
  • v-zhangti's avatar
    v-zhangti
    3 years ago

    Hi, polman4 

     

    You can try the following methods.

    Calculated Column:

    Sum =
CALCULATE (
    SUM ( 'Table'[Sales] ),
    FILTER (
        'Table',
        [Product] = EARLIER ( 'Table'[Product] )
            && [Year] = EARLIER ( 'Table'[Year] )
    )
)

    Revenue = SWITCH(TRUE(),
[Sum]>=0&&[Sum]<=10,"0 to 10",
[Sum]>10&&[Sum]<=20,"11 to 20",
[Sum]>20&&[Sum]<=30,"21 to 30")

    Result:

    Is this the result you expect?

     

    Best Regards,

    Community Support Team _Charlotte

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

  • rsbin's avatar
    rsbin
    3 years ago

    polman4 ,

    Since I don't have your new file, I will make a couple of suggestions:

    1) Create a new Calculated Column:  Year = YEAR( Append1[Time] ).  This will make things easier to see as you continue to progress.

    I believe you have the Filter in the wrong spot in your new Sites_per_year table.

    Sites_per_year = 
SUMMARIZECOLUMNS(
        Append1[Year], 
        Append1[Name (Site)],
        "Inventory", sum(Append1[Total inventory]),
        FILTER (Append1, Append1[Year] = 2022))

    Give this a shot and hopefully you can continue on your way.

    Regards,

     

rsbin's avatar
rsbin
Community Champion
3 years ago

polman4 ,

Since I don't have your new file, I will make a couple of suggestions:

1) Create a new Calculated Column:  Year = YEAR( Append1[Time] ).  This will make things easier to see as you continue to progress.

I believe you have the Filter in the wrong spot in your new Sites_per_year table.

Sites_per_year = 
SUMMARIZECOLUMNS(
        Append1[Year], 
        Append1[Name (Site)],
        "Inventory", sum(Append1[Total inventory]),
        FILTER (Append1, Append1[Year] = 2022))

Give this a shot and hopefully you can continue on your way.

Regards,

 

polman4's avatar
polman4
Helper I
3 years ago

Thank you rsbin 

This worked fine!

For anyone reading the thread this is final code i used to create the summarize table:

Sites_per_year = 
SUMMARIZECOLUMNS(
        Append1[c.Year],
        Append1[Name (Site)],
        Filter(Append1, Append1[c.Month] = 01 
                        || Append1[c.Month] = 02 
                        || Append1[c.Month] = 03
                        || Append1[c.Month] = 04
                        || Append1[c.Month] = 05
                        || Append1[c.Month] = 06
                        || Append1[c.Month] = 07
                        || Append1[c.Month] = 08
                        || Append1[c.Month] = 09),
        "Inventory", sum(Append1[Total inventory])
    )