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$,

- between 10001$ and 19999$

- more than 20000$

 

My dataset has more than 7m rows so I created a sample one.

So here is a simple sum of each product per year

and here is what i achieve and i what i need

Here is a sample file: https://we.tl/t-LPRNcQkPzl
The revenue margins are custom by me, 
I don't know what the best way to create the custom revenue margins, i tried to create another table with just one column and dragging this column which it works but i don't know how to "filter" distinct count with this value.

 

Any help would be appreciated! Thank you

 

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,

     

5 Replies

  • rsbin's avatar
    rsbin
    Community Champion
    3 years ago

    polman4 ,

    Please see revised pbix file attached.

    First created a Summary Table that creates your annual totals.

    Then a Calculated Column using SWITCH to determine which "Revenue Bin" each product applies to.

     

    Trust this is what you are after.

    Regards,

  • v-zhangti's avatar
    v-zhangti
    Community Support
    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.

  • polman4's avatar
    polman4
    Helper I
    3 years ago

    Hello both,

     

    Thank you so much for the time spent, really really helpful info and I learned a lot also.
    A note: My Append table instead of a column named "Year" has a Date/time field.
    Let me make some comments:

    rsbin 
    Your solution worked perfect for me.


    I just tried an extra step but i wasn't successful. I tried to add a filter to summarizecolumn

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

    Now the column year doesn't excist anymore and it's turned to "Time" with date hierarchy. And it's not really working:

    Also i dont have all the categorizations for a reason. If I use 

    v-zhangti 
    Your example is what i wanted but the problem is i can't replicate it on the original data.. I think the caclulations I do on the new calculated column are wrong:

    b.sum_per_year = 
CALCULATE(
    SUM(Append1[Total inventory]),
    FILTER(
        'Append1',
        [Name (Site)] = EARLIER('Append1'[Name (Site)])
            && Append1[Time].[Year] = EARLIER(Append1[Time].[Year])
    )
)

    and here is the result:

     

    I think on both occasions my problem is that i don't know how to handle correctly a column with date/time structure..

    Any addition help would be usefull 🙂

    Thank you,

    Greg

    • 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])
    )