Forum Discussion

Mahadevaraobc's avatar
Mahadevaraobc
Helper II
7 years ago

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....
Need Help
v-yulgu-msft's avatar
v-yulgu-msft
Microsoft Employee
7 years ago

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

Mahadevaraobc's avatar
Mahadevaraobc
Helper II
7 years ago

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

AA811327.27%

 

Now i will be adding Country as  a new filter, so if US is selected table should show only numbers of US

eg : 

AA76-1-16.67%

and when canada is selected it should display

AA15480.00%

 

Hope im clear with my explanation now.