topic Re: Issue with Summarize in Power Pivot in DAX Commands and Tips

Issue with Summarize in Power Pivot

Ackbar-Learner — Tue, 25 Oct 2022 07:13:07 GMT

Hi I am having with Summarize function in Power Pivot Dax

Below are four dependent measures one after the other. As you can see for the measure Projected Amount (EUR), the subtotals are correct but the last one just after tax is not deducting the tax. Please see the Dax for each measure below:

The issue seems to lie between the 2nd and 3rd measure. I cannot get the Summarize function to work correctly in the 2nd measure so that the 3rd measure (Running Total) can read it and apply same in its formula.

Any help would be greatly appreciated.

Best Regards

Re: Issue with Summarize in Power Pivot

Ackbar-Learner — Tue, 25 Oct 2022 07:26:58 GMT

amitchandak grateful for any idea?

Re: Issue with Summarize in Power Pivot

tamerj1 — Tue, 25 Oct 2022 08:29:39 GMT

Hi Ackbar-Learner
Try to add the equal sign

Re: Issue with Summarize in Power Pivot

Ackbar-Learner — Tue, 25 Oct 2022 08:34:28 GMT

tamerj1 no effect mate🤔

Re: Issue with Summarize in Power Pivot

tamerj1 — Tue, 25 Oct 2022 08:38:07 GMT

Hi Ackbar-Learner
Would you please copy/paste the code of the four measures?

Re: Issue with Summarize in Power Pivot

Ackbar-Learner — Tue, 25 Oct 2022 09:18:49 GMT

Sure tamerj1

1st Measure:

=VAR Result = [AmountWithSign]

VAR TaxApplicable = -1*if([TaxCalcCumulative]>[TaxCalcPeriod],[TaxCalcPeriod],[TaxCalcCumulative])

RETURN

calculate(if(if(HASONEVALUE(TabPLGLItem[GLItem]),values(TabPLGLItem[GLItem]))="Tax",TaxApplicable,Result))

2nd Measure:

=VAR CorrectTotal = SUMMARIZE(TabPLGLItem,[GLItem],"__value",[TaxAmountWithSign])

RETURN

IF(HASONEVALUE(TabPLGLItem[GLItem]),[TaxAmountWithSign],SUMX(CorrectTotal,[__value]))

3rd Measure:

=if(

HASONEFILTER(TabPLGLItem[GLItem])

CALCULATE(

[PLTaxAmountWithSign]

ALL(TabPLGLItem[GLItem])

TabPLGLItem[Index] <= VALUES(TabPLGLItem[Index])

)

blank()

)

4th Measure:

=SWITCH(

[HeaderCalcType]

BLANK(),BLANK()

1,[TaxAmountWithSign]

2,[RunningTotal]

)

Re: Issue with Summarize in Power Pivot

tamerj1 — Tue, 25 Oct 2022 10:05:14 GMT

Ackbar-Learner
Sorry I got distracted with some other business.
Please use the following for measure 2

2nd Measure: = SUMX ( VALUES ( TabPLGLItem[GLItem] ), [TaxAmountWithSign] )

Re: Issue with Summarize in Power Pivot

Ackbar-Learner — Tue, 25 Oct 2022 10:16:41 GMT

This was so easy and I went to fetch it on the moon😃. Any clue why did the Summarize function not work?

Also, would you mind explaining what is actually the above function doing? This will be for the benefit of people who will read this afterwards and myself as well.

Re: Issue with Summarize in Power Pivot

tamerj1 — Tue, 25 Oct 2022 10:36:17 GMT

Ackbar-Learner
You were blanking out the total using HASONEVALUE which was absolutely unnecessary

Re: Issue with Summarize in Power Pivot

Ackbar-Learner — Tue, 25 Oct 2022 10:51:11 GMT

Thanks tamerj1