Total of Monthly/Quarterly/Yearly Revenue calculated wrong

[Anonymous]

Hi,

I have a measure, which calculates the monthly revenue. However, when I add the the data to a table, I get wrong total calculations as can be seen below:

Would anyone know what I am doing wrong?

[v-frfei-msft]

Hi @Anonymous ,

Please try to create another measure based on that.

New measrue = SUMX('Monthly Revenue Table',[Budget per month])

[Anonymous]

@v-frfei-msft 

Not sure why, but it still gives the same result as if I had the 'Grand Total' toggled on in the Matrix that I have - 719k

[az38]

Hi @Anonymous 

it doesnt look like error, it looks like round question.

pick your Measure at the right pane, go to Modeling ribbon and set desired Decimal places as 2

do not hesitate to give a kudo to useful posts and mark solutions as solution

[Anonymous]

@az38 

The totals are calculating wrong:

Oct rev: 43

Nov rev: 568

Dec rev: 192

That is 803, not 599

Same goes for 2020 and total of 2019+2020

[az38]

@Anonymous 

didnt you try CALCULATE over SUMX?

CALCULATE(SUMX(....))

do not hesitate to give a kudo to useful posts and mark solutions as solution

[Anonymous]

@az38 

I tried, but I it does not seem to work..

Total Monthly Revenue = CALCULATE(SUMX('Monthly Revenue Table',[Budget per month]))

This calculation still shows the wrong total values

[az38]

@Anonymous 

no. what kind of values show this measure?

Budget per month = 
CALCULATE(
SUMX (
    ADDCOLUMNS (
        FILTER (
            'Monthly Revenue Table',
            'Monthly Revenue Table'[Include in financial forecast] = "Yes"
                && NOT (
                    'Monthly Revenue Table'[Estimated Start Time] > MAX ( 'Calendar'[Date] )
                        || 'Monthly Revenue Table'[Estimated Finish Time] < MIN ( 'Calendar'[Date] )
                )
        ),
        "AverageBudget", 'Monthly Revenue Table'[Current Client Budget]
            / (
                DATEDIFF (
                    'Monthly Revenue Table'[Estimated Start Time],
                    'Monthly Revenue Table'[Estimated Finish Time],
                    MONTH
                ) + 1
            )
    ),
    [AverageBudget]
)
)

do not hesitate to give a kudo to useful posts and mark solutions as solution

[Anonymous]

@az38 

That measure basically calculates the "Monthly Revenue", which can be seen in the above table. So the monthly revenue is correct, but since it has a row context, it calculates the totals wrong. I have found the following two videos, but I am not that good at realising how to implement them into my own measure:

https://www.youtube.com/watch?v=44QUjA5NDwY

https://www.youtube.com/watch?v=Ka7Ds4EAjNQ

[mussaenda]

you must also show your calculation

[Anonymous]

Here is the measure of monthly revenue:

Budget per month = 
SUMX (
    ADDCOLUMNS (
        FILTER (
            'Monthly Revenue Table',
            'Monthly Revenue Table'[Include in financial forecast] = "Yes"
                && NOT (
                    'Monthly Revenue Table'[Estimated Start Time] > MAX ( 'Calendar'[Date] )
                        || 'Monthly Revenue Table'[Estimated Finish Time] < MIN ( 'Calendar'[Date] )
                )
        ),
        "AverageBudget", 'Monthly Revenue Table'[Current Client Budget]
            / (
                DATEDIFF (
                    'Monthly Revenue Table'[Estimated Start Time],
                    'Monthly Revenue Table'[Estimated Finish Time],
                    MONTH
                ) + 1
            )
    ),
    [AverageBudget]
)

This is the column calculation:

Monthly Revenue = 
IF (
    'Monthly Revenue Table'[Include in financial forecast] = "Yes",
    DIVIDE (
        'Monthly Revenue Table'[Current Client Budget],
        DATEDIFF (
            'Monthly Revenue Table'[Estimated Start Time],
            'Monthly Revenue Table'[Estimated Finish Time],
            MONTH
        ) + 1
    ),
    BLANK ()
)

[amitchandak]

Both Side of > and < then are there. No <= or >=. Any specific reason.  This can leave the start or end of the month ?

[Anonymous]

Not sure I follow  @amitchandak ,

are you talking about this part of the measure:

'Monthly Revenue Table'[Estimated Start Time] > MAX ( 'Calendar'[Date] )
                        || 'Monthly Revenue Table'[Estimated Finish Time] < MIN ( 'Calendar'[Date] )

[amitchandak]

Yes. Both sides do not cover = date, any reason for that?

[Anonymous]

@amitchandak 

I had help writing the measure. It does the job for me, basically calculating the monthly revenue by dividing the "Current Client Budget" by the amount of months that the "Project" spans over.

Should I add "=" to those?

[amitchandak]

Looking at the formula it seems you need that. <= and >= , after that check the number.  If still did not match. Let us know.

[Anonymous]

@amitchandak,

I am afraid it doesn't solve the problem.

As I said I believe it might have something to do with the fact the row context has a filter whereas the totals do not as shown in this video:

https://www.youtube.com/watch?v=Ka7Ds4EAjNQ

I am trying to work my way through, but it is quite difficult for me

[amitchandak]

Where is the grouping for the month year?   I think you want to group data at month year level and then use that. The is no column added in addcolumns to group by data at month year. Unless I missed it. Add there or try summarize like this

Budget per month = 
SUMX (
    summarize (
        FILTER (
            'Monthly Revenue Table',
            'Monthly Revenue Table'[Include in financial forecast] = "Yes"
                && NOT (
                    'Monthly Revenue Table'[Estimated Start Time] > MAX ( 'Calendar'[Date] )
                        || 'Monthly Revenue Table'[Estimated Finish Time] < MIN ( 'Calendar'[Date] )
                )
        ),<Month>,<Year>,
        "AverageBudget", 'Monthly Revenue Table'[Current Client Budget]
            / (
                DATEDIFF (
                    'Monthly Revenue Table'[Estimated Start Time],
                    'Monthly Revenue Table'[Estimated Finish Time],
                    MONTH
                ) + 1
            )
    ),
    [AverageBudget]
)

You need two group data on month year. So add one or more column as  group bys

[Anonymous]

How exactly am I supposed to use that function you have written.

Am I supposed add one column for each month? Like:

Budget per month = 
SUMX (
    summarize (
        FILTER (
            'Monthly Revenue Table',
            'Monthly Revenue Table'[Include in financial forecast] = "Yes"
                && NOT (
                    'Monthly Revenue Table'[Estimated Start Time] > MAX ( 'Calendar'[Date] )
                        || 'Monthly Revenue Table'[Estimated Finish Time] < MIN ( 'Calendar'[Date] )
                )
        ),Month(1),Year(2019),
        "AverageBudget", 'Monthly Revenue Table'[Current Client Budget]
            / (
                DATEDIFF (
                    'Monthly Revenue Table'[Estimated Start Time],
                    'Monthly Revenue Table'[Estimated Finish Time],
                    MONTH
                ) + 1
            )
    ),
    [AverageBudget]
)

And then for each month?

Can you give an example?

[amitchandak]

Examples

Budget per month = 
SUMX (
    summarize (
        FILTER (
            'Monthly Revenue Table',
            'Monthly Revenue Table'[Include in financial forecast] = "Yes"
                && NOT (
                    'Monthly Revenue Table'[Estimated Start Time] > MAX ( 'Calendar'[Date] )
                        || 'Monthly Revenue Table'[Estimated Finish Time] < MIN ( 'Calendar'[Date] )
                )
        ),'Monthly Revenue Table'[Month],'Monthly Revenue Table'[Year],
        "AverageBudget", 'Monthly Revenue Table'[Current Client Budget]
            / (
                DATEDIFF (
                    'Monthly Revenue Table'[Estimated Start Time],
                    'Monthly Revenue Table'[Estimated Finish Time],
                    MONTH
                ) + 1
            )
    ),
    [AverageBudget]
)

or
Budget per month = 
SUMX (
    summarize (
        FILTER (
            'Monthly Revenue Table',
            'Monthly Revenue Table'[Include in financial forecast] = "Yes"
                && NOT (
                    'Monthly Revenue Table'[Estimated Start Time] > MAX ( 'Calendar'[Date] )
                        || 'Monthly Revenue Table'[Estimated Finish Time] < MIN ( 'Calendar'[Date] )
                )
        ),'Monthly Revenue Table'[Month-Year],
        "AverageBudget", 'Monthly Revenue Table'[Current Client Budget]
            / (
                DATEDIFF (
                    'Monthly Revenue Table'[Estimated Start Time],
                    'Monthly Revenue Table'[Estimated Finish Time],
                    MONTH
                ) + 1
            )
    ),
    [AverageBudget]
)

or

Budget per month = 
SUMX (
    summarize (
        FILTER (
            'Monthly Revenue Table',
            'Monthly Revenue Table'[Include in financial forecast] = "Yes"
                && NOT (
                    'Monthly Revenue Table'[Estimated Start Time] > MAX ( 'Calendar'[Date] )
                        || 'Monthly Revenue Table'[Estimated Finish Time] < MIN ( 'Calendar'[Date] )
                )
        ),'Date'[Month],'Date'[Year],
        "AverageBudget", 'Monthly Revenue Table'[Current Client Budget]
            / (
                DATEDIFF (
                    'Monthly Revenue Table'[Estimated Start Time],
                    'Monthly Revenue Table'[Estimated Finish Time],
                    MONTH
                ) + 1
            )
    ),
    [AverageBudget]
)

[Anonymous]

@amitchandak 

So these are three different options?

If I use this:

Budget per month = 
SUMX (
    summarize (
        FILTER (
            'Monthly Revenue Table',
            'Monthly Revenue Table'[Include in financial forecast] = "Yes"
                && NOT (
                    'Monthly Revenue Table'[Estimated Start Time] > MAX ( 'Calendar'[Date] )
                        || 'Monthly Revenue Table'[Estimated Finish Time] < MIN ( 'Calendar'[Date] )
                )
        ),'Monthly Revenue Table'[Month],'Monthly Revenue Table'[Year],
        "AverageBudget", 'Monthly Revenue Table'[Current Client Budget]
            / (
                DATEDIFF (
                    'Monthly Revenue Table'[Estimated Start Time],
                    'Monthly Revenue Table'[Estimated Finish Time],
                    MONTH
                ) + 1
            )
    ),
    [AverageBudget]
)

Then I need to add a Month/Year Date to the 'Monthly Revenue Table', however, since projects span over several months (sometimes starting in one year and ending in another) how do I get around that problem then?