Measure Totals, The Final Word
Greg_Deckler You, sir, just saved my day... THANKS!!!
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> (4 Months)]) -- Tried to re-calculate at day level
- SUMX(VALUES('Accounting Period'[Year Month Number]),[ <Measure> (4 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.
- 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.
SUMX(
SUMMARIZE(<Fact Table>, [Fact Table][Column1], [Fact Table][Column2], [Fact Table][Column3], "Measure Name", <Measure>),
<Measure Name>)
Will this works for all dimensions. I hope with this we might hit with performance issue.
Please suggest some taughts to find solution for this