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