Forum Discussion

ldamato's avatar
ldamato
Frequent Visitor
4 years ago

Small Multiples Sum Wrong Values

Hi all, 

 

I'm using small multiples to create a chart that gets the running sum of each broker I have. 

To do the running sum, I use this formula (which works great): 

Measure = CALCULATE(SUM(Sheet1[Value]), FILTER(ALLSELECTED(Sheet1), Sheet1[date] <= MAX(Sheet1[date])))
 
The problem is that when I filter more than one small multiple, the values gets messed up.
Example: 
If I select only broker 1:  THIS IS CORRECT 

 

If I select a second one on the filter: WRONG, The first one sums the value second one (14M) and both become wrong:

Does anyone know how I could solve this please:

Sample data: smallmultiples_sample.xlsx

 

Thanks in advance

Leo

 

 

Need Help

4 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    4 years ago

    You can't use ALLSELECTED in that scenario and you shouldn't use MAX in the way you do.

    cumul = 
var d = max(Sheet1[date])
return CALCULATE(sum(Sheet1[Value]),Sheet1[date]<=d)

    see attached

     

     

     

    • ldamato's avatar
      ldamato
      Frequent Visitor
      4 years ago

      Hi lbendlin , 

       

      Thanks for the reply. I tested it and it's pretty much what I need. The only issue is that I couldn't make it work using Date Hierarchy, only with the full date value. Is there anything I can do to make this work? 

       

      Thanks a lot

      Leo 

      • lbendlin's avatar
        lbendlin
        Super User
        4 years ago

        The use of date hierarchies is discouraged. Use a proper calendar table in your data model.