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.

 

CountrySerial Noactualbudgetdiff%
USA?8978-11-14.10%
USAAA76-1-16.67%
USAAB23133.33%
USABC127-5-71.43%
USADD45346181.74%
USAFF196216209.26%
USAGG183170-13-7.65%
USAHH1214214.29%
USAII1100.00%
USAJJ107-3-42.86%
USAKK2102473714.98%
USALL14614600.00%
USAMM40674093260.64%
USANN1100.00%
canada?1045918-127-13.83%
canadaAA15480.00%
canadaAB8640-46-115.00%
canadaBC19991899-100-5.27%
canadaDD42-2-100.00%

 

What i want is:

1) When no county is selected all serial number values should be summed up and show diff and diff in %

2) if any country is selected only serial numbers to that country should be summed and diff and diff in % should be shown

Need Help

2 Replies

  • 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.