Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Variance Analysis where Current Month data is blank

Hi All,

Thank you in advance for helping me.  I've been using BI for a year or so, and I'm finally getting to create some interesing conent.  My latest roadblock is in how to calculate variance analysis where the current month data is blank at the lowest level (i.e. product). The following table illustrates my problem.  Revenue Actual for Products C & D is 0.  The underlying data is acutally blank - no sales for the current month.  It displays as zero, fine, but then all of my variance columns are blank - no calcuations.  No matter what I do, I can't seem to get BI to display the proper result.  Rev Var Disc SPLY should be -12,331 and all other should be 0.

 

CustNoPartCodeRevenue ActualRevenue SPLYYoY Rev VarRev Price ActualRev Price SPLYRev Price Var SPLYRev Var Vol SPLYRev Var Disc SPLYRev Var New SPLYRev Var Mix SPLY
1A276,9720276,9720.440.00000276,9720
2B82,503243,359-160,8579.889.880-160,857000
1C012,331-12,3310.002.14     
2D056,920-56,9200.005.97     

 

My calculations are:

Not displayed - calulates from my source query:   Total Revenue = If(CALCULATE(Sum('Variance'[InvTotal]))=Blank(),0,CALCULATE(Sum('Variance'[InvTotal])))
 
Revenue Actual = CALCULATE([Total Revenue],FILTER(DataVersion, 'DataVersion'[DataVersion]="Actual"))
 
Revenue SPLY = CALCULATE([Total Revenue],SAMEPERIODLASTYEAR('Dates5'[Dates]),FILTER(DataVersion, 'DataVersion'[DataVersion]="Actual"))
 
YoY Rev Var = CALCULATE([Total Revenue],'DataVersion'[DataVersion]="Actual") - CALCULATE([Revenue SPLY],'DataVersion'[DataVersion]="Actual")
 
Rev Price Actual = if(isblank([Revenue Actual]),0,DIVIDE([Revenue Actual],[Units Actual],0))
 
Rev Price SPLY = DIVIDE([Revenue SPLY],[Units SPLY],0)
 
Rev Price Var SPLY = IF([Rev Price Actual]=0,0,IF([Rev Price Actual]*[Rev Price SPLY]<>0,([Rev Price Actual]-[Rev Price SPLY])*[YoY Unit Var],0))
 
Rev Var Vol SPLY = IF([Units SPLY]*[Units Actual]<>0,([Units Actual]-[Units SPLY])*[Rev Price SPLY],0)
 
Rev Var Disc SPLY = IF(isblank([Revenue Actual]),[YoY Rev Var],0)
 
Rev Var New SPLY = IF([Rev Price SPLY]=0 && [Rev Price Actual]<>0,[Revenue Actual],0)
 
Rev Var Mix SPLY = [YoY Rev Var]-([Rev Var Vol SPLY1]+[Rev Price Var SPLY1]+[Rev Var New SPLY1]+[Rev Var Disc SPLY1])
 
Any thoughts?
Need Help

1 Reply

  • v-chenwuz-msft's avatar
    v-chenwuz-msft
    Icon for Community Support rankCommunity Support
    4 years ago

    Hi Anonymous ,

     

    Revenue Actual = CALCULATE([Total Revenue],FILTER(DataVersion, 'DataVersion'[DataVersion]="Actual"))
     
    Revenue SPLY = CALCULATE([Total Revenue],SAMEPERIODLASTYEAR('Dates5'[Dates]),FILTER(DataVersion, 'DataVersion'[DataVersion]="Actual"))
     
    In my opinion, C & D have no rows in the fact table rathen have some rocords with the sales value is 0.  So the filte context of this c & d always blank.
     
    Workaround,
    1 add rows with C & D in fact table and value is 0
     
    2 add if() outside these colunm to make sure it not be blank.
     

    Best Regards

    Community Support Team _ chenwu zhu

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.