cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Ackbar-Learner
Resolver I
Resolver I

Issue with Summarize in Power Pivot

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:

AckbarLearner_0-1666681404480.png

AckbarLearner_1-1666681574143.png

AckbarLearner_2-1666681601239.png

AckbarLearner_3-1666681661069.png

AckbarLearner_4-1666681686135.png

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

 

1 ACCEPTED SOLUTION

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

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

View solution in original post

9 REPLIES 9
tamerj1
Super User
Super User

Hi @Ackbar-Learner 
Try to add the equal sign 

1.png

@tamerj1 no effect mate🤔

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

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]

)

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

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

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.

 

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

Thanks @tamerj1 

Ackbar-Learner
Resolver I
Resolver I

@amitchandak grateful for any idea?

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.

Top Solution Authors
Top Kudoed Authors