Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
RadMich
Frequent Visitor

Show monthly, quarterly, annual value for each product

Hello,

 

I have to build a matrix with a Product column and a Budget column, however each product needs to show the monthly, quarterly, annual budget. Here's a visual of what I'm trying to accomplish:

  Budget
Product AFiscal Month2282
 Fiscal Quarter2734
 Fiscal Year3226
Product BFiscal Month2402
 Fiscal Quarter2878
 Fiscal Year3396
Product CFiscal Month3226
 Fiscal Quarter4532
 Fiscal Year5865

 

The Budget value comes from a measure:

mBudget = CALCULATE(SUM('Sales'[Sales_USD]),'Sales'[cDataSetForForecast]="Budget")
Product is a column in the Product table
Fiscal Month, Fiscal Quarter and Fiscal Year are columns in the Date table.
 
I need to achieve the above design upon the user selecting a Fiscal Month on a slicer. In other words, when the user selects a fiscal month, the monthly, quarterly and annual values are automatically calculated.
 
Any help would be much appreciated.
Thank you in advance
1 ACCEPTED SOLUTION

Hi @RadMich ,

Add a row “Total” to the Products column of the Products table.

vdengllimsft_0-1733205386485.png


And the measure is as follows.

Budget = 
VAR result= 
SWITCH( SELECTEDVALUE(Period[Period]), "Fiscal Month", [MonthlyBudget], "Fiscal Quarter", [QuarterlyBudget], "Fiscal Year", [AnnualBudget] )
RETURN

IF(SELECTEDVALUE('Product'[Product])<>"Total",result,
SWITCH( 
    SELECTEDVALUE(Period[Period]), 
    "Fiscal Month", CALCULATE([MonthlyBudget],ALL('Product'[Product])) , 
    "Fiscal Quarter",CALCULATE([QuarterlyBudget],ALL('Product'[Product])),
    "Fiscal Year", CALCULATE([AnnualBudget],ALL('Product'[Product])) 
    )
)


The final result is as follows. Hopefully it will meet your needs.

vdengllimsft_1-1733205511962.png

 

Please see the attached pbix for reference.

Best Regards,
Dengliang Li

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

View solution in original post

6 REPLIES 6
v-denglli-msft
Community Support
Community Support

Thanks for the reply from VahidDM, please allow me to add some more suggestions.
Hi @RadMich 

You only need to make the following changes in the measure provided by the VahidDM.

Budget = 
VAR result= 
SWITCH(SELECTEDVALUE(Period[Period]),"Fiscal Month", [MonthlyBudget], "Fiscal Quarter", [QuarterlyBudget], "Fiscal Year", [AnnualBudget] )
    
RETURN
SWITCH(
      TRUE(),    
ISFILTERED(
'Product'[Product])=FALSE(),CALCULATE([mBudget],ALL('Period'[Period]),ALL('Product'[Product])),
ISFILTERED('Period'[Period])=FALSE(),CALCULATE([mBudget],ALL('Period'[Period])),
result)

vdengllimsft_0-1733108804435.png

 

Please see the attached pbix for reference.

Best Regards,
Dengliang Li

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

Hello Dengliang Li,

 

thank you so much for your input, but it does not provide the solution I need. I'm looking for a total by Fiscal Month, Fiscal Quarter and Fiscal Year, as follows. Could you please help to achieve this result? Thank you

 

Product AFiscal Month2282
 Fiscal Quarter2784
 Fiscal Year3226
Product BFiscal Month2402
 Fiscal Quarter2878
 Fiscal Year3396
Product CFiscal Month3226
 Fiscal Quarter4532
 Fiscal Year5865
TotalFiscal Month7910
 Fiscal Quarter10194
 Fiscal Year12487

Hi @RadMich ,

Add a row “Total” to the Products column of the Products table.

vdengllimsft_0-1733205386485.png


And the measure is as follows.

Budget = 
VAR result= 
SWITCH( SELECTEDVALUE(Period[Period]), "Fiscal Month", [MonthlyBudget], "Fiscal Quarter", [QuarterlyBudget], "Fiscal Year", [AnnualBudget] )
RETURN

IF(SELECTEDVALUE('Product'[Product])<>"Total",result,
SWITCH( 
    SELECTEDVALUE(Period[Period]), 
    "Fiscal Month", CALCULATE([MonthlyBudget],ALL('Product'[Product])) , 
    "Fiscal Quarter",CALCULATE([QuarterlyBudget],ALL('Product'[Product])),
    "Fiscal Year", CALCULATE([AnnualBudget],ALL('Product'[Product])) 
    )
)


The final result is as follows. Hopefully it will meet your needs.

vdengllimsft_1-1733205511962.png

 

Please see the attached pbix for reference.

Best Regards,
Dengliang Li

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

Hello v-denglli-msft, Thank you so much, I appreciate your help.

VahidDM
Super User
Super User

Hi @RadMich 

 

 

 

You can achieve this by creating a custom "Period" table and using measures to calculate the budget for each period. Here's how:

  1. Create a Period Table:

Create a new table named Period with the following DAX:

Period = DATATABLE( "Period", STRING, { {"Fiscal Month"}, {"Fiscal Quarter"}, {"Fiscal Year"} } )

 

 

  1. Create Measures for Each Period:
  • Monthly Budget:

MonthlyBudget = CALCULATE( [mBudget], ALL('Date'), 'Date'[Fiscal Month] = SELECTEDVALUE('Date'[Fiscal Month]), 'Date'[Fiscal Year] = SELECTEDVALUE('Date'[Fiscal Year]) )

 

  • Quarterly Budget:
  •  

QuarterlyBudget = CALCULATE( [mBudget], ALL('Date'), 'Date'[Fiscal Quarter] = SELECTEDVALUE('Date'[Fiscal Quarter]), 'Date'[Fiscal Year] = SELECTEDVALUE('Date'[Fiscal Year]) )

  • Annual Budget:

AnnualBudget = CALCULATE( [mBudget], ALL('Date'), 'Date'[Fiscal Year] = SELECTEDVALUE('Date'[Fiscal Year]) )

  1. Create a Combined Measure:

BudgetMeasure = SWITCH( SELECTEDVALUE(Period[Period]), "Fiscal Month", [MonthlyBudget], "Fiscal Quarter", [QuarterlyBudget], "Fiscal Year", [AnnualBudget] )

  1. Build Your Matrix Visual:
  • Rows:
    • Add Product from your Product table.
    • Add Period from the Period table.
  • Values:
    • Add BudgetMeasure.
  1. Add a Fiscal Month Slicer:
  • Use Date[Fiscal Month] as a slicer.
  • When a month is selected, the measures calculate budgets for that month, its quarter, and its year.

Notes:

  • Ensure your Date table includes Fiscal Month, Fiscal Quarter, and Fiscal Year columns.
  • The measures use ALL('Date') to remove existing date filters and apply new ones based on the selected period. It would be better to adjust this and use ALL(with column from table)

By setting up your data this way, your matrix will display each product with monthly, quarterly, and annual budget values that update based on the selected fiscal month.

 

 

 

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.

Appreciate your Kudos!! 

 

LinkedIn|Twitter|Blog |YouTube 

Hello VahidDM,

 

thank you so much, this is exactly what I needed. 

I'm trying to show a total for all products by Fiscal Month, Fiscal Quarter, Fiscal Year by activating Row Subtotals,, but it's not showing any value. Would you be able to help with that as well? Thank you

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.