Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Display sales inside a fix range

Hi folks,
I`m kind new to Dax and need help for some specific requirement. 
I have a measure that checks the total sales grouped by client inside a range (contained in a table called Range) and I have a slicer (with only one selection - radio button) that allows user to filter data sales on specific range. my measure gives the right result when it comes to only one selection (Please see below).
Now, the users want to be able to filter on two or more ranges and I don't know how to make power bi compares sales for each range selected
let me give you more context.

First, here's the ranges we have : 


And the measure created for only one selection is : 

TotalSalesRange = 
Var Virtualtable = Summarize( Sales, Sales[clientid],"sales",sum(Sales[Amount]))
Var MinRangeSelected = SelectedValue (Range[min])

Var MaxRangeSelected = SelectedValue (Range[max])
Return
 SUMX ( 
                          Filter( Virtualtable, [sales]>= MinRangeSelected && [sales]< MaxRangeSelected)

                          [sales]
                        )
Does anyone know how I can created the measure that will consider all ranges selected ?

 

Thanks for your help.

RedGC

 

 

 

 

Need Help

17 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super User
    5 years ago

    Hi, Anonymous 

    Please check the below picture and the sample pbix file's link down below, whether it is what you are looking for.

    I tried to create a sample that suits your request.

    All measures are in the sample pbix file.

     

     

     

    https://www.dropbox.com/s/5nb9e89jsc20079/red.pbix?dl=0 

     

     

    Hi, My name is Jihwan Kim.

    If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

    Linkedin: https://www.linkedin.com/in/jihwankim1975/

    • Syndicate_Admin's avatar
      Syndicate_Admin
      Administrator
      3 years ago

      Hello. How did you manage to solve it? I have the same problem. I need to do a customer counter in a sales range every month of the year. I mean, I need something like this.

      I have this information:

      And I need to build something like this:

      It's similar to your solution. please help me? as you built it. Thank you.

      • Jihwan_Kim's avatar
        Jihwan_Kim
        Super User
        3 years ago

        Hi,

        I am not sure if I understood your question correctly, but please check the below picture and the attached pbix file.

         

         

         

        Clients count: =
VAR _minrange =
    MIN ( Range[Min] )
VAR _maxrange =
    MAX ( Range[Max] )
VAR _salestable =
    ADDCOLUMNS (
        SUMMARIZE ( Sales, Client[Client] ),
        "@sales", CALCULATE ( SUM ( Sales[Sales] ) )
    )
VAR _rangetable_clientlist =
    SUMMARIZE (
        FILTER ( _salestable, [@sales] > _minrange && [@sales] <= _maxrange ),
        Client[Client]
    )
RETURN
    IF ( HASONEVALUE ( 'Month'[Month] ), COUNTROWS ( _rangetable_clientlist ) )

         

         

         

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi,
    Thanks Jihwan_Kim and Ashish_Mathur  for your reply.


    Sorry guys, but I just noticed that I missed a detail in the description of my message. Actually, what I'm looking to have is the total sales of the sales that are in the specified ranges. for example, let's take the following example. 
    I have sales for 2020 for two customers:

    and We have the following range as a Slicer: 
    $0 - 100K
    $100K - $200K.
    $200K - $300K.
    $300K - $400K.
    $400K - $500K.
    $500K - $600K.


    Now, My question is how can I get the total sales of those sales that are between $100K - $200K and the sales between $300K - $400K  ( assuming that  users have selected  $100K - $200K and $300K - $400K  in the Slicer)


    In Excel, I would filtered first my data by $100K - $200K and $300K - $400K and then, calculate the total sales  which give me the followin result : 

     

    I hope this example helps you well understant my case.
    Thanks for your help.
    RedGC

     





     

     

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi Jihwan_Kim , 

    Thanks alot for your reply and effort. 
    your solution works good when we want to go through all groups, but my requirement is the user has to select the group(s)/range(s) in a slicer and based on it the calculation will happen for the total sales. 

    I tried used ALLSELECT(Group[min]) and because it returns a table, it can be used in your the formula. 
    Thanks,

    RedGC

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    I meant ALLSELECTED(Group[min])

  • v-cazheng-msft's avatar
    v-cazheng-msft
    Community Support
    5 years ago

    Hi Anonymous 

    I'm not sure what field you are using as the slicer, but you can try a Measure as following.

     

    TotalSalesRange = CALCULATE(SUM(Sales[Sales]),ALLSELECTED(Range[range]))

     

    The result looks like this:

     

    For more details, you can refer the attached pbix file.

     

    Best Regards

    Caiyun Zheng

     

    Is that the answer you're looking for? If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.