Forum Discussion
Helper IGrand Total ALL() with filter
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
Helper IAnonymous
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
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
- MINGXINYeah, it worked. So cool, thanks Anonymous.BTW, I want to share my formula with you if you have distinct value in your end.BRSoldier完工数量_new =//sumx(Values('Fact_QTY_Cost'[产成品订单号]) , calculate(distinct('Fact_QTY_Cost'[完工数量])))Var _product = ALLSELECTED('Fact_QTY_Cost'[产成品订单号])Var _SUTYPE = ALLSELECTED('Fact_QTY_Cost'[CATEGORY1])ReturnCALCULATE(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)