cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
bbsin
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

 

bbsin_0-1659074452705.png

 

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

 

bbsin_1-1659074573248.png

 

 

1 ACCEPTED SOLUTION
v-jayw-msft
Community Support
Community Support

Hi @bbsin ,

 

Please try following the steps.

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.

View solution in original post

2 REPLIES 2
v-jayw-msft
Community Support
Community Support

Hi @bbsin ,

 

Please try following the steps.

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.
amitchandak
Super User
Super User

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

https://radacad.com/pivot-and-unpivot-with-power-bi

 

Create date using month

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

 

Join sales and budget table with common date table and analyze

Helpful resources

Announcements
PBI Sept Update Carousel

Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

Learn Live

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Dashboard in a day with date

Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

MPPC 2023 PBI Carousel

Power Platform Conference-Power BI and Fabric Sessions

Join us Oct 1 - 6 in Las Vegas for the Microsoft Power Platform Conference.

Top Solution Authors