cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

New Member

## Can advise how to show the total budget vs spending by month and quarter and yearly?

hi

the budget cost I have is in this table format,

May I know how to show in the dashboard

by it's sub cat and detail breakdown to

1) Qtr total Budget vs total spending

2) Monthly -

3) yearly as it will few yeards

not sure how to link into the date as the budget dates are in SUM?  Thanks

1 ACCEPTED SOLUTION
Community Support

Hi @bbsin ,

1# Unpivot the yearmonth columns.

2# Create Year, Quarter and Month Columns.

3# Create measures like below:

``````Qtr =
DIVIDE (
CALCULATE (
SUM ( [value] ),
ALLEXCEPT ( 'table', [category], [subcategory], [Year], [Quarter] )
),
CALCULATE (
MAX ( [total] ),
ALLEXCEPT ( 'table', [category], [subcategory], [Year] )
)
)

Monthly =
DIVIDE (
CALCULATE (
SUM ( [value] ),
ALLEXCEPT ( 'table', [category], [subcategory], [Year], [Month] )
),
CALCULATE (
MAX ( [total] ),
ALLEXCEPT ( 'table', [category], [subcategory], [Year] )
)
)

Yearly =
DIVIDE (
CALCULATE (
SUM ( [value] ),
ALLEXCEPT ( 'table', [category], [subcategory], [Year] )
),
CALCULATE (
MAX ( [total] ),
ALLEXCEPT ( 'table', [category], [subcategory] )
)
)
``````

Best Regards,

Jay

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.
2 REPLIES 2
Community Support

Hi @bbsin ,

1# Unpivot the yearmonth columns.

2# Create Year, Quarter and Month Columns.

3# Create measures like below:

``````Qtr =
DIVIDE (
CALCULATE (
SUM ( [value] ),
ALLEXCEPT ( 'table', [category], [subcategory], [Year], [Quarter] )
),
CALCULATE (
MAX ( [total] ),
ALLEXCEPT ( 'table', [category], [subcategory], [Year] )
)
)

Monthly =
DIVIDE (
CALCULATE (
SUM ( [value] ),
ALLEXCEPT ( 'table', [category], [subcategory], [Year], [Month] )
),
CALCULATE (
MAX ( [total] ),
ALLEXCEPT ( 'table', [category], [subcategory], [Year] )
)
)

Yearly =
DIVIDE (
CALCULATE (
SUM ( [value] ),
ALLEXCEPT ( 'table', [category], [subcategory], [Year] )
),
CALCULATE (
MAX ( [total] ),
ALLEXCEPT ( 'table', [category], [subcategory] )
)
)
``````

Best Regards,

Jay

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.
Super User

@bbsin , You have to unpivot the months. remove the total months.

Create date using month

Date = datevalue("01 "& [Month] )

Join sales and budget table with common date table and analyze