Grand Total ALL() with filter

[xuexi1890]

Hello,

I have created a measure 

Grand Total = CALCULATE(sum(Table1[Resale Qty]),all(Table1[Resale Price]))

when i don't filter Product, everything seems correct. P1

when i filter Product, then, the Grand Total is wrong, do you know how can I avoid it? thanks

the Power Bi Desktop file is in below URL.

https://1drv.ms/u/s!Am-wyNUhKsP7gx4yc41fsNF5Ne_E?e=GSePU9

[Mariusz]

Hi @xuexi1890 

Try this.

Grand Total = 
CALCULATE(
    SUM( Table1[Resale Qty] ),
    ALL( Table1 )
)

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.

[xuexi1890]

@Mariusz 

thank you Mariusz,

if i try Grand Total = CALCULATE(sum(Table1[Resale Qty]),ALL(Table1)), then this number is grand total of the Table1,

but i want it to be, the grand total of the product within whatever filter i have given. 

ie. the grand total has to be affected by slicers, but not respond to prices

regards

nate 

[CheenuSing]

Hi @xuexi1890 ,

I was playing around with the attached pbix.

1.The matrix visual is based on ReSale Price.

2. The table visual is based on Product.

So there is no commanility between the two. And hence when you use visual filter on product on  the table visual you will not get same results.

You also have a Page Level filter on Product, if you use the filtering on Product using this the result is the same in both visuals.

The page level filter applies to all the visuals in a page.

Hope this clarifies.

Cheers

CheenuSing

[xuexi1890]

@CheenuSing 

thanks CheenuSing.

perhaps, i didn't fully understand your point. but for me, i want my grand total respond to the slicers, while keeping the grand total per product.

let me rephrase my request in a simpler way. how can i get the grand total equal to the other 2 grand totals shown below. ( i have slicers on product, resale invoice month and maybe more on sales area)

[CheenuSing]

Hi  @xuexi1890 ,

Please try

Grand Total =

Var _product = SELECTEDVALUE(Table1[Product])

Return

CALCULATE(sum(Table1[Resale Qty]),ALL(Table1),Table1[Product] = _product)

This will then display the grand total for the Product[ID] value. 

Let me know if this works.

Cheers

CheenuSing

[xuexi1890]

@CheenuSing 

thanks, but it is not responding to my slicers. it is a grand total ignoring my slicers.

how can i tell DAX that my dataset of the ALL() is narrowed down because of the slicer.

Cheers

Nate

[CheenuSing]

Hi @xuexi1890 ,

 I am still not clear of your requirement.

Can you please put in excel file the output you expect for the sample data  when

1.  No slicer for Product ID is selected

2. When a slicer for Product ID is selected

From where you select the product , is it from the table visual or from the Visual Filter in the Visualisation Pane for the table visual.

Cheers

CheenuSing

[xuexi1890]

@CheenuSing 

i want my grand total all equal to 4,609,550, instead of 1,821,000 or 2,788,500

if you play with the slicer here, when filter nothing on the product, the result is correct or you only select one month, instead of 2 months.

https://1drv.ms/u/s!Am-wyNUhKsP7gx-eCJTTqjWCv93O

[CheenuSing · Accepted solution]

Hi @xuexi1890 ,

I think i got the solution at last

Grand Total =
VAR _product =
    ALLSELECTED ( Table1[Product] )
VAR _Month =
    ALLSELECTED ( Table1[Resale Invoice Month] )
RETURN
    CALCULATE (
        SUM ( Table1[Resale Qty] ),
        ALL ( Table1 ),
        Table1[Product] IN _product,
        Table1[Resale Invoice Month] IN _Month
    )

1.When no filter on Product or Month is selected it computes the total for the entire table.

2.When a Filter on Product alone is selected it computes the total for only that product for all months in the table.

3. When a filter on Month alone is selected it computes the total for only those Months for all products.

4. When a Product and Month is filtered, it computes the total for only those Products and those Months selected.

This should work.

Cheers

CheenuSing

[MINGXIN]

Yeah, it worked. So cool, thanks @CheenuSing.

BTW, I want to share my formula with you if you have distinct value in your end.

BR

Soldier

完工数量_new =

//sumx(Values('Fact_QTY_Cost'[产成品订单号]) , calculate(distinct('Fact_QTY_Cost'[完工数量])))

Var _product = ALLSELECTED('Fact_QTY_Cost'[产成品订单号])

Var _SUTYPE = ALLSELECTED('Fact_QTY_Cost'[CATEGORY1])

Return

CALCULATE(sumx(Values('Fact_QTY_Cost'[完工数量]) , calculate(distinct('Fact_QTY_Cost'[完工数量]))),ALL('Fact_QTY_Cost'),'Fact_QTY_Cost'[产成品订单号] IN _product,'Fact_QTY_Cost'[CATEGORY1] IN _SUTYPE)

[MINGXIN]

Update the formula:

完工数量_new =

Var _product = ALLSELECTED('Fact_QTY_Cost'[产成品订单号])

Var _SUTYPE = ALLSELECTED('Fact_QTY_Cost'[CATEGORY1])

Return

CALCULATE(sumx(Values('Fact_QTY_Cost'[完工数量]) , calculate(distinct('Fact_QTY_Cost'[完工数量]))),ALL('Fact_QTY_Cost'),'Fact_QTY_Cost'[产成品订单号] IN _product,'Fact_QTY_Cost'[CATEGORY1] IN _SUTYPE)