Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Variance between Percent to Grand Total Columns

Hello,

 

I am trying to show the variance between the Recvd U + Curr OO Pen. % & Recvd U LY Pen. % columns. Both are shown as a percent to grand total via the "Show Value as" drop down under values.

 

Variance Column:

China = 1.3% (49.0% - 47.7%)

United States of America = -5.5% (37.8% - 43.3%)

Vietnam = 1.6% (3.2% - 1.6%)

Etc....

 

 

Thank you!

Need Help
Tips and Tricks

4 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

    Anonymous , if these are measures

    new measure =

    [Recvd U + Curr OO Pen. %]  - [Recvd U LY Pen. %]

     

    or

     

    new measure =

    sumx(values([County of manufacturing]) , [Recvd U + Curr OO Pen. %]  - [Recvd U LY Pen. %])

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Hi amitchandak this is not working... The only measure is Recvd U + Curr. OO U (It is adding the 2 fields). Recvd U LY is only a field from the "COO Report" Table. I've attached a screenshot for reference of the data set:

  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    Icon for Community Support rankCommunity Support
    5 years ago

    Hi Anonymous , 

     

    IF the Recvd U + Curr OO U is a measure and Recvd U LY is a column, You could create a measure by the following formula:

    Variance1 = [Recvd U + Curr. OO U]-MAX([Recvd U LY])

    Or

    Variance2 =
VAR _new =
    SUMMARIZE (
        'Table',
        'Table'[Country of Manufacture],
        [Recvd U LY],
        "1", [Recvd U + Curr. OO U])
RETURN
    SUMX ( _new, [1] - [Recvd U LY] )

     The final output is shown below:

     

    Best Regards,
    Community Support Team_ Yalan Wu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.  

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Hi v-yalanwu-msft amitchandak 

       

      I tried the measure and this is not working... For reference I am trying to show a % variance between: Recvd U+ Curr. OO Pen% vs. Recvd U LY Pen. %. I'm currently doing Pen. % by throwing the measure for Recvd U + Curr. OO U & Recvd U LY field and hitting "Show value as" & "Percent of grand total".

       

      The measure you suggested is Variance1 and is not showing as a percent variance.

       

      Variances

      China = +6%

      USA = -9%

      Vietnam = +2%