Forum Discussion
Variance Percent
Hi Everyone, Im having difficulty in achieving the following, please help. I have created a Table visual as below. Country Serial No actual budget diff % USA ? 89 78 -11 -14....
Hi Mahadevaraobc ,
Please refer to below measures.
Total Diff =
IF (
ISFILTERED ( Table2[Country] ),
CALCULATE ( SUM ( Table2[budget] ), ALL ( Table2[Serial No] ) )
- CALCULATE ( SUM ( Table2[actual] ), ALL ( Table2[Serial No] ) ),
CALCULATE ( SUM ( Table2[budget] ), ALL ( Table2[Country] ) )
- CALCULATE ( SUM ( Table2[actual] ), ALL ( Table2[Country] ) )
)
Total % =
IF (
ISFILTERED ( Table2[Country] ),
DIVIDE (
(
CALCULATE ( SUM ( Table2[budget] ), ALL ( Table2[Serial No] ) )
- CALCULATE ( SUM ( Table2[actual] ), ALL ( Table2[Serial No] ) )
),
CALCULATE ( SUM ( Table2[budget] ), ALL ( Table2[Serial No] ) )
),
DIVIDE (
(
CALCULATE ( SUM ( Table2[budget] ), ALL ( Table2[Country] ) )
- CALCULATE ( SUM ( Table2[actual] ), ALL ( Table2[Country] ) )
),
CALCULATE ( SUM ( Table2[budget] ), ALL ( Table2[Country] ) )
)
)
Best regards,
Yuliana Gu
Thank You,
But this answers part of my question. In my report i need to have serial no combined for all countries and actual, budget should be summed. and diff should be calculated.
eg: in the same above table Sl no for US and Canada should be added, that will be shown as follows
AA 8 11 3 27.27% Now i will be adding Country as a new filter, so if US is selected table should show only numbers of US
eg :
AA 7 6 -1 -16.67% and when canada is selected it should display
AA 1 5 4 80.00% Hope im clear with my explanation now.
