Forum Discussion
Possible Analytics function: dynamic Total column in chart with USA States?
Anonymous,
Drag [State] from the calculated table to Axis and measures below to Value.
Measure = VAR s = SELECTEDVALUE ( 'Table'[State] ) RETURN IF ( s = "Total", SUM ( ValueTable[Regular jobs] ), CALCULATE ( SUM ( ValueTable[Regular jobs] ), FactTable[State] = s ) )Measure 2 = VAR s = SELECTEDVALUE ( 'Table'[State] ) RETURN IF ( s = "Total", SUM ( ValueTable[Bottleneck jobs] ), CALCULATE ( SUM ( ValueTable[Bottleneck jobs] ), FactTable[State] = s ) )
Anonymous,
Drag [State] from the calculated table to Axis and measures below to Value.
Measure =
VAR s =
SELECTEDVALUE ( 'Table'[State] )
RETURN
IF (
s = "Total",
SUM ( ValueTable[Regular jobs] ),
CALCULATE ( SUM ( ValueTable[Regular jobs] ), FactTable[State] = s )
)
Measure 2 =
VAR s =
SELECTEDVALUE ( 'Table'[State] )
RETURN
IF (
s = "Total",
SUM ( ValueTable[Bottleneck jobs] ),
CALCULATE ( SUM ( ValueTable[Bottleneck jobs] ), FactTable[State] = s )
)
Hi v-chuncz-msft;
thank you for your measure codes.
But unfortunately there's no other result:
No total dataYour measure codeConnecting Tables
Do you have any ideas why there's no solution?
BR,
Sven
Anonymous,
Remove the relationship between FactTable and Table.
Dear v-chuncz-msft,
Can you advise on adding an index column in calculated table 'Table', so I can sort the order of tenure categories on my chart properly? Otherwise the solution works great!
Thanks.
<img>https://fhi360web-my.sharepoint.com/:i:/g/personal/mmahachi_fhi360_org/EfYZ7h1Q_MtCh0Djb1F9UkEBtMtz_7VTwZ5-rIzj6Dj1ig?e=yxlCKP</img>
The solution didn't work for me, could it be because I only have the "total" name in a lookup table and not the fact?
