Forum Discussion

Gaurav_84's avatar
Gaurav_84
Helper I
1 year ago
Solved

Variance calculation dex

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

 

 

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

2 Replies

  • 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

     

  • v-ssriganesh's avatar
    v-ssriganesh
    Community Support

    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.