Forum Discussion

vojtechsima's avatar
vojtechsima
Icon for Super User rankSuper User
4 years ago
Solved

Matrix - Total to All when filtered

Hi, guys,
I am quite struggling with this one:
I need this table:

BUSomethingTotal by BUTotal by ItemTotal by selected BU
Elle5500706892765008608966
Louis1000154003965008608966
Total6500860896665008608966


I need to get the Total of "Total by BU" to Total by selected BU. 
I am able to retrieve the Total By BU using this measure:

 

 

var __SelectedBUs = VALUES('sample'[BU])

var __calc = CALCULATE(SUM('sample'[Something]),ALL('sample'[Item],'sample'[BU]), 'sample'[BU] IN __SelectedBUs)

 

 

However, I can't figure out how to put the total to All rows in the next column.
There's an active "Item" slicer:


What it should do is that it takes all "BUs" that is filtered by "Item", then toss the "Item" filter out, SUM(Something) of all the filtered BUs together and return it back to all rows. Similar to "Total By BU" but return the total to all rows.

  • vojtechsima's avatar
    vojtechsima
    4 years ago

    Hi, amitchandak 
    I am a bit embarrassed that I didn'T figure this out, however, I managed to fix it by referencing the previous measure and adding the ALL filter.

    var __calc = CALCULATE('sample'[AllexceptBU], ALL('sample'[BU]))
    return __calc

    However, I appreciate your time and effort. Thank you

3 Replies

  • vojtechsima , try if one of the two can help

     

    var __SelectedBUs = VALUES('sample'[BU])

    var __calc = CALCULATE(SUM('sample'[Something]),Keepfilters('sample'[BU] IN __SelectedBUs)

    or


    var __SelectedBUs = VALUES('sample'[BU])

    var __calc = CALCULATE(SUM('sample'[Something]),'sample'[BU] IN __SelectedBUs)

    • vojtechsima's avatar
      vojtechsima
      Icon for Super User rankSuper User

      Hi, amitchandak 
      I am a bit embarrassed that I didn'T figure this out, however, I managed to fix it by referencing the previous measure and adding the ALL filter.

      var __calc = CALCULATE('sample'[AllexceptBU], ALL('sample'[BU]))
      return __calc

      However, I appreciate your time and effort. Thank you

    • vojtechsima's avatar
      vojtechsima
      Icon for Super User rankSuper User

      Hi, amitchandak 
      Unfortunately, this gives me something that I already did in previous tries, I am still not getting the same result for all rows in my matrix.
      I need to have the sum of mentioned BUs in all rows (the same value) and I also need to filter out the "Item" slicer.