AAS - Excel Pivot Grand Total Not correct

[Anonymous]

I have fact table with 8 dimensions and average grand total should work based on selected dimension. Please guide me How to correct the grand total based on selections

User validating this data from AAS using excel plugin. We can’t tell what dimension is going to selected for analysis

Measure 1 = SUM(Amount)

Measure 2 = Total days in 3 Months (Need to ignore the missing months based on dimension). 

Avg = Measure 1 / Measure 2

Sample data

Days	Period	Client	Channel	Region	Agent	Product	Amount
31	202001	A1	C1	R1	A1	P1	100
29	202002	A1	C1	R1	A1	P1	200
31	202003	A1	C1	R1	A3	P2	300
31	202001	A2	C1	R1	A3	P4	150
29	202002	A3	C1	R1	A1	P1	250
31	202003	A2	C1	R1	A1	P1	350
31	202003	A2	C2	R2	A2	P1	450
Report Based on Client	Amount	Days	Avg
	C1	1350	91	14.84
	C2	450	31	14.52
		1800	91	19.78	Dax Calculation (Days in 3 Months At grand total)
				29.35	Expected Avg Total
Report Based on Agent	Amount	Days	Avg
	A1	900	91	9.89
	A2	450	62	7.26
	A3	450	31	14.52
		1800	91	19.78	Dax Calculation (Days in 3 Months At grand total)
				31.66	Expected Avg Total

Tried some possible options to fix in AAS. 

• SUMX(VALUES('Accounting Period'[Effective Date]),[ <Measure> (3 Months)])  -- Tried to re-calculate at day level
• SUMX(VALUES('Accounting Period'[Year Month Number]),[ <Measure> (3 Months)])  -- Tried to re-calculate at Month level
• Var Days = [DaysIn4Months]

           Return

          SUMX(Table,(Table[Column] / Days) -- Tried calculate at each row level .

• If we use AVERAGEX function it is giving wrong average due to it is considering the no of days Transaction received (per month 21 / 22 days only – as we are not receiving transactions on weekends). User want to consider full days in month.

To correct the grand totals we need to use below syntax. But this syntax we can't achieve while using Pivot tables as we can't tell what slicer is going to select.

• IF(HASONEVALUE([Slicer]), <Measure> , SUMX(VALUES[Slicer]), <Measure>) 

Hi All,

Any ideas on above issue. AAS should calculate average grand total based on selected slicer in excel pivot table. I am happy to write multiple if conditions if we have solution for this. 

How to find what all slicers selected to apply if condition.

Please suggest some taughts to find solution for this

[technolog · Accepted solution]

The grand total for the average isn't working as expected when using Excel Pivot tables connected to AAS. The challenge is that you can't predict which dimension will be selected in the slicer, so you need a dynamic solution.

The main issue is that the grand total for the average should be based on the sum of the amounts divided by the total days for the selected dimension, rather than the average of the averages.

Here's a way to approach this:

First, you need to calculate the total days dynamically based on the selected dimension. You can use the SELECTEDVALUE function to check if a single value is selected in a dimension. If multiple values or none are selected, it returns an alternative result.

For the total days in 3 months, you can use something like:

TotalDaysMeasure =
IF(
NOT ISBLANK(SELECTEDVALUE('Table'[Client])),
SUMX(FILTER('Table', 'Table'[Client] = SELECTEDVALUE('Table'[Client])), 'Table'[Days]),
IF(
NOT ISBLANK(SELECTEDVALUE('Table'[Agent])),
SUMX(FILTER('Table', 'Table'[Agent] = SELECTEDVALUE('Table'[Agent])), 'Table'[Days]),
SUM('Table'[Days])
)
)
This DAX checks if a client is selected, and if so, sums the days for that client. If not, it checks if an agent is selected and sums the days for that agent. If neither is selected, it sums all days. You can expand this logic for other dimensions as needed.

Next, for the average calculation, you can use:

AvgMeasure =
DIVIDE(
SUM('Table'[Amount]),
[TotalDaysMeasure]
)
This divides the total amount by the dynamically calculated total days.

Now, when you use this AvgMeasure in a pivot table, it should give you the correct average for each row and for the grand total, based on the selected slicer.

Remember, the key is to dynamically calculate the total days based on the selected dimension, and then use that in your average calculation. This way, the grand total will be based on the overall sum of amounts divided by the overall total days for the selected dimension, rather than an average of averages.