Total Row Miscalculation

[BuckedUpJJP]

I have created the following measure for my dataset and it is correctly calculating each month properly but my total row isn't displaying the correct total. How can I adjust my measure to calculate the total correctly?

There are two tables I am using in this measure:
1. dim_Budgets table that was uploaded via a flat file. In file, I have the following columns - Customer Type, Budget Account, Product Type, date (this is the last day of each month), month, year, value. This Budget table is only for 2024.
2. dim_Dates table which is a calendar table displaying all days of the year from 1/1/2023 to 12/31/2025. 

In my dim_Budget table I needed to expand my dates field to include every day of the year. I used power query editor to replace my dates (last day of each month) and expand it for each day of the year. By doing so my budget amount total for each month is on each date line. For example, 1/31/2024 budget total is 17,960,418 which is now displaying on every row from 1/1/2024 to 1/31/2024. February budget total is 13,073,316 which is now displaying one every row from 2/1/2024 to 2/29/2024 and so on with each month. 

Here is the measure I created to calculate the daily amount:

Daily Budget =

DIVIDE(

    CALCULATE(

        SUM(dim_budgets[Value]),

        dim_budgets[Budget Account] = "Gross Revenue",  -- Filter for Gross Revenue

        NOT(dim_budgets[Customer Type] IN {"Consolidated", "Corp"})  -- Exclude Customer Types "Consolidated" and "Corp"

    ),

    CALCULATE(

        COUNTROWS(dim_dates),

        FILTER(

            ALL(dim_dates),

            dim_dates[Year] = 2024 &&  -- Filter by the current year

            dim_dates[Month] = MAX(dim_dates[Month])  -- Filter by the current month

        )

    )

)

[Ritaf1983]

Hi @BuckedUpJJP 

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Please show the expected outcome based on the sample data you provided.

Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[BuckedUpJJP]

Here is my dataset for my Dim_Budgets table:  https://drive.google.com/file/d/1K4mceCNQaLKBVnYuQp9CLBLC9nCXhQIq/view?usp=drive_link

Here is my dataset for my Dim_Dates table: 

https://drive.google.com/file/d/1LF9-fqlCQfrcaVGKDT2qs0qSCXiXgpmf/view?usp=drive_link

[Anonymous · Accepted solution]

Thanks for the attention to the problem from Ritaf1983.

Hi @BuckedUpJJP ,

I don't have access to the link you provided, so I created simple test data:

The reason the total row is incorrect is that it adds up all the values and divides by 31, whereas there are not actually 31 days in every month. At this point we can create another measure:

Final Budget = SUMX(VALUES('dim_dates'[Month]),[Daily Budget])

Result:

Best Regards,
Zhu

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

[BuckedUpJJP]

Thank you for your reply this worked to fix my issue!