Variance Analysis where Current Month data is blank

[Anonymous]

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.

CustNo	PartCode	Revenue Actual	Revenue SPLY	YoY Rev Var	Rev Price Actual	Rev Price SPLY	Rev Price Var SPLY	Rev Var Vol SPLY	Rev Var Disc SPLY	Rev Var New SPLY	Rev Var Mix SPLY
1	A	276,972	0	276,972	0.44	0.00	0	0	0	276,972	0
2	B	82,503	243,359	-160,857	9.88	9.88	0	-160,857	0	0	0
1	C	0	12,331	-12,331	0.00	2.14
2	D	0	56,920	-56,920	0.00	5.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?

[v-chenwuz-msft]

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.