Variance calculation dex

[Gaurav_84]

Hi Experts,

Need your support in calculating variance using dex. Below is how my data look like. 

Table 1 is the plan values for the whole year & table 2 is actual spent for the whole year. Maximum variance allowed is 15% of plan values. i need to display top 10 accounts whose variance is more than 15% when comparing Plan vs actual. 

for example account AC1 total plan for the year is $24k & maximum variance allowed is 15% which mean $3600. where as the total spent for the year is $32K as per table 2. So if i compare Plan VS actual my total difference is $8K (24K-32K). which is 20% of plan value.

So i need to show this in my bar chart as account AC1 variance% = 20 & amount 8K.

Regards

[v-ssriganesh · Accepted solution]

Hello @Gaurav_84,
Thank you for reaching out to the Microsoft Fabric Community Forum.

I have reproduced your scenario in Power BI Desktop and I got the expected output as per your requirement. The bar chart highlights the top 10 accounts with variance greater than 15%, and the table visual shows Plan YTD, Actual YTD, Variance Amount, and Variance %. Please find the attached .pbix file for your reference.

Best Regards,
Ganesh singamshetty.

[jaineshp]

Hey @Gaurav_84,

I'll help you create a variance calculation solution in Power BI. Here's a step-by-step approach:

Solution Steps:

1. Create Calculated Columns:

Plan Total = SUMX(Table1, [Jan]+[Feb]+[Mar]+[Apr]+[May]+[Jun]+[Jul]+[Aug]+[Sep]+[Oct]+[Nov]+[Dec])

Actual Total = SUMX(Table2, [Jan]+[Feb]+[Mar]+[Apr]+[May]+[Jun]+[Jul]+[Aug]+[Sep]+[Oct]+[Nov]+[Dec])

Variance Amount = [Actual Total] - [Plan Total]

Variance % = ABS([Variance Amount]) / [Plan Total]

2. Filter Top 10 Accounts:

• Add a filter: Variance % > 0.15
• Use Top N filter on Variance % (Descending, 10 accounts)

3. Bar Chart Setup:

• X-axis: Account
• Y-axis: Variance %
• Tooltips: Add Variance Amount for detailed view
• Data Labels: Show Variance % as percentages

4. Format the Visual:

• Sort by Variance % (highest first)
• Use conditional formatting to highlight accounts >15%
• Add reference line at 15% threshold

5. Final Touch:

Variance Display =
IF([Variance %] > 0.15,
"Variance: " & FORMAT([Variance %], "0%") & " | Amount: $" & FORMAT([Variance Amount]/1000, "0") & "K",
BLANK())

This will give you exactly what you need - top 10 accounts exceeding 15% variance with both percentage and dollar amounts displayed.

Fixed? ✓ Mark it • Share it • Help others!

Best Regards,
Jainesh Poojara | Power BI Developer

[Gaurav_84]

Thanks @jaineshp : can you please also suggest me how to calculate year to date Plan values. I tried using TOTALYTD formula but it didnt work.

for example if we are in month of august it should sum all the values till current month.

regards

[jaineshp]

Hey @Gaurav_84,

Here's the working solution for YTD Plan calculation:

Step 1: Create YTD Plan Measure

YTD Plan =
VAR CurrentMonth = MONTH(TODAY())
RETURN
IF(CurrentMonth >= 1, [Jan], 0) +
IF(CurrentMonth >= 2, [Feb], 0) +
IF(CurrentMonth >= 3, [Mar], 0) +
IF(CurrentMonth >= 4, [Apr], 0) +
IF(CurrentMonth >= 5, [May], 0) +
IF(CurrentMonth >= 6, [Jun], 0) +
IF(CurrentMonth >= 7, [Jul], 0) +
IF(CurrentMonth >= 8, [Aug], 0) +
IF(CurrentMonth >= 9, [Sep], 0) +
IF(CurrentMonth >= 10, [Oct], 0) +
IF(CurrentMonth >= 11, [Nov], 0) +
IF(CurrentMonth >= 12, [Dec], 0)

Step 2: Create YTD Actual

YTD Actual = [Apply same formula to your Actual table]

Step 3: YTD Variance

YTD Variance % = DIVIDE([YTD Actual] - [YTD Plan], [YTD Plan])

Since you're in August, it'll sum Jan through Aug automatically.

Fixed? ✓ Mark it • Share it • Help others!

Best Regards,
Jainesh Poojara | Power BI Developer