Different level of summary based on slicer value?

[itsmebvk]

Hi Folks,

I am trying to achieve a requirement, I tried different ways but couldn't reach a solution.

Sample Report and File  attached 

Measure 1 = When I select an Item in Item slicer, I want to get the total of "QTY" of the "Item Line" which selected slicer values belong to.

 For example when I select Item 1001 in slicer, it should get Sum(QTY) for all A1  "Item Line"  as 1001 belongs to A1  "Item Line" , in this example it should give 65. 

  

A1  "Item Line"   Total 

When I select 1008 it should give total B1 "Item Line" total(QTY) as 1008 belongs to B1 "Item Line" in this case it should give 190

B1 "Item Line" 

  Measure 2 = When I select 1001 it should give Category A total , when I select 1009 it should give Category  B total

A total should be 150 

B total should be 385 

These measure should also change based on Date or any other slicer we have in the report..

Please suggest, Thank you.

[parry2k]

Hey I am away from my desk. Check my video which came out today and it has a solution using remove filters and that will work, will keep the filter on date but will remove only from item.

[parry2k]

@itsmebvk Glad to hear it worked out. As a matter of fact, I did a video on this and that will be out tomorrow (Wednesday, March 16th) and in that video, I went with two alternate solutions, using ALLEXCEPT and REMOVEFILTERS functions.

I will post the link to the video here once it is published. Do subscribe to my YT channel, if you haven't already, I usually make videos on real-time business problems or questions asked on the community forum. 

[itsmebvk]

Awesome good to hear that. Yes I already subscribed and following your LinkedIn page. Once again than you and awaiting for new video . 

[parry2k · Accepted solution]

@itsmebvk these measures will do it without using Item Line and Item Category  in the table visual

Item Line Total = CALCULATE ( SUM ( 'Fact'[QTY] ),  ALLEXCEPT( Dim, Dim[Item Line] ), VALUES ( Dim[Item Line] ) )

Item Category Total = CALCULATE ( SUM ( 'Fact'[QTY] ),  ALLEXCEPT( Dim, Dim[Category] ), VALUES ( Dim[Category] ) )

Here is the output if 1001 is selected.

✨ Follow us on LinkedIn and  to our YouTube channel

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop shop for Power BI-related projects/training/consultancy.

[itsmebvk]

@parry2k  Apologies.

I missed to test one more scenario. I also need to apply a filter on top of the date along with expression mentioned below. But it is returning the value for specific ITem_Line I selected for 😞

Item Line Total = CALCULATE ( SUM ( 'Fact'[QTY] ),  ALLEXCEPT( Sub Category,Dim, Dim[Item Line] ), VALUES ( Sub Category ) )



Item Line Total = CALCULATE ( SUM ( 'Fact'[QTY] ), filter(Date< Date(2020,01,01)), ALLEXCEPT( Sub Category,Dim, Dim[Item Line] ), VALUES ( Sub Category ) )

[itsmebvk]

@parry2k @lbendlin  Is there anyway we apply this Date(Column in The same Dim table) filter along with Allexcept on same Dim table?

[itsmebvk]

@parry2k  Awesome that really worked. Thanks alot for your inputs.

[lbendlin]

Here is a starting point. Adjust as needed .

Parent Qty =
CALCULATE (
    SUM ( 'Fact'[QTY] ),
    ALL ( 'Fact' ),
    CALCULATETABLE ( VALUES ( 'Fact'[Item] ), ALLEXCEPT ( Dim, Dim[Item Line] ) )
)

[itsmebvk]

@lbendlin  Thank you so much for quick reply.

Yes, I already did this earlier and it is working at Item_Line level, but this approach is not working when you try to get summary for subcategory or Category it belongs to. is there anything I am missing?

[lbendlin]

You can use the same approach. The only thing you have to change is the ALLEXCEPT scope.

[itsmebvk]

@lbendlin  do you mean like this? it should be 30 instead 855, As I highlighted in screenshot I only want to show ITEM in table not Sub Category and Category. That's where the approach was not working . If I add Sub Category  or Category it works.

Sub Cat Total =

CALCULATE (

SUM ( 'Fact'[QTY] ),

ALL ( 'Fact' ),

CALCULATETABLE ( VALUES ( 'Fact'[Item] ), ALLEXCEPT(Dim,Dim[Sub Category] )))

[lbendlin]

Not sure what you do different - it works for me.

pbix attached.

[itsmebvk]

@lbendlin really sorry if haven't mentioned that. It was not working because i dont want to Category or Sub Category in table. However your direction helped me alot . Thanks alot for your inputs.