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

Brass Contributor

# 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:

3 Replies

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

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!

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

sorry maar hoe maakt ik een nieuwe discussie?

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

Ga naar de hoofdpagina van het forum en klik dan op "Start a Discussion":