Forum Discussion

agagnon's avatar
agagnon
Frequent Visitor
7 years ago
Solved

Table Summary Showing Incorrect Total

Hello,   I have been trying to create a measure that will allow me to look at a data source (actual revenues & expenses) and return the total if there is data for that month and otherwise return th...
Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago

    I belive this is a data lineage problem. Once you reference the BudgetOrActualTotal table you've created, it doesn't seems to identify the original column on the data model to filter your measure. I think you could try the following:

     

    Projection Using Budget = 
VAR BudgetOrActual = IF( ISBLANK( [Total Actual] ), [Total Budget], [Total Actual] )

RETURN
IF( HASONEVALUE( Dates[MonthName] ),
    BudgetOrActual,
        SUMX( VALUES(Dates[MonthName] ), IF( ISBLANK( [Total Actual] ), [Total Budget], [Total Actual] ) ) )
agagnon's avatar
agagnon
Frequent Visitor
7 years ago

I have also tried the follow code which provided a different but also incorrect result.  In the first result it appears the SUMX formula is summing total actual.  In the second result it looks like SUMX is calculating everything but then doing it over and over for each month.  For example, if I use my slicer to show only 1 month everything works.  If I use my slicer to show 2 months the total is as shown  below which is adding the total for each month together twice.  If I add a 3rd month it adds each month togehter three times.  I hope that makes sense!

 

Projection Using Budget V2 = 
VAR BudgetOrActual = IF(ISBLANK([Total Actual]),[Total Budget],[Total Actual])
VAR BudgetOrActualTotal = SUMMARIZE(Dates,Dates[MonthName],"Monthly Amount", BudgetOrActual )

RETURN
IF( HASONEVALUE(Dates[MonthName]),BudgetOrActual,SUMX(BudgetOrActualTotal,[Monthly Amount]))