Forum Discussion

johnsboxftm's avatar
johnsboxftm
Brass Contributor
Apr 01, 2023

Fill Handle Not Working. Is there a better way to do this?

Hi all,

 

I am calculating Sales Variances from month to month and previous year.  I already used a pivot table to gather the information.  I would have liked to have had the dollar amount and its % variance in the same cell instead of having to make 8 new cells for each month but I tried googling it and watching you tube and I can't find anything about what I am asking for specifically.

If I could make each column look like this

 

                              January '23            February '23          March '23

Sales                        $100,000                $95,000                $80,000

Last Month               $90,000                $100,000              $100,000

Variance            +$10,000 (+10%)      -$5,000 (-5%)      -$20,000 (-20%)

Last Year                  $75,000                  $90,000                $50,000

Variance            +$25,000 (+25%)      +$5,000 (+5%)   +$30,000 (+60%)

 

I would like the percentage to be in the same cell as the variance $  ... Just can't figure it out.

 

Here is a pic of how I have it now:

 

Thank you for your time.

 

 

BI & Data Analysis
excel
Formulas and Functions

3 Replies

  • NikolinoDE's avatar
    NikolinoDE
    Platinum Contributor
    Apr 02, 2023

    johnsboxftm 

    To create a custom measure in Power Pivot, you need to use the Data Analysis Expressions (DAX) formula language.

    Here’s an example of how you can create a custom measure that concatenates the dollar amount variance and its percentage variance into a single text string:

     

    1. Open the Power Pivot window by clicking on the “Power Pivot” tab in the ribbon and selecting “Manage”.
    2. In the Power Pivot window, select the table where you want to create the custom measure.
    3. Click on the “Measure” button in the “Calculations” group of the “Home” tab.
    4. In the formula bar, enter a DAX formula that calculates the dollar amount variance and its percentage variance and concatenates them into a single text string.

    For example: 

    Variance = FORMAT([Sales]-[Last Month],"$#,##0") & " (" & FORMAT(([Sales]-[Last Month])/[Last Month],"0.0%") & ")".

    Make sure to replace [Sales] and [Last Month] with the appropriate measure names for your data.

    1. Press Enter to save the measure.

     

    You can now add this custom measure to your Power Pivot table to display both the dollar amount variance and its percentage variance in the same cell.

     

    I hope this helps! 

Resources