Help to create quarter and subtotal columns in Power BI

[BFAGUNDES]

Hi everyone,

I'm looking for help creating aggregated subtotal columns in Power BI based on Fiscal Quarters.

Specifically, I need to:

Create a column to displaythe subtotal  for Fiscal Year 2024 (grouping all FY24 quarters);Then, show the individual quarters of FY25, followed by a subtotal for FY25

After that, group and subtotal the years from FY26 to FY29

Finally, create another column that aggregates data from FY16 through FY29.

I'm struggling with how to structure this logic using DAX or Power Query. Any suggestions or guidance would be greatly appreciated!

Thanks in advance!

follow 

TABLE exemple:

KPI Name	Value	FY Quarter	Fiscal Year
LOE, m/d	-11.09	FY19	FY24 Q1
On-Shore LOE, m/d	1,660.74	FY23	FY24 Q2
Off-Shore LOE, m/d	-4.00	FY13	FY24 Q3
On-Shore, %	1.31	FY14	FY24 Q4
Current Month Bookings(reported), $	-48.63	FY15	FY25 Q1
New order, $	1,107.71	FY16	FY27 Q3
T&M CAP, $	300.31	FY17	FY25 Q2

[Ashish_Mathur]

Hi,

Does your data have a Date or a month column.  If yes, then share that data.  Also, from which month does the FY start?

[v-tsaipranay]

Hi @BFAGUNDES ,

We haven’t received an update from you in some time. Could you please let us know if the issue has been resolved?
If you still require support, please let us know, we are happy to assist you.

Thank you.

[v-tsaipranay]

Hi @BFAGUNDES ,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions.

Thank you.

[v-tsaipranay]

Hi @BFAGUNDES ,

Thank you for reaching out to the Microsoft fabric community forum. 

Could you please confirm if the issue has been resolved. I wanted to check if you had the opportunity to review the information provided by @danextian and @johnt75 . Please feel free to contact us if you have any further questions.

Thank you.

[danextian · Accepted solution]

Hi @BFAGUNDES 

You'll have to create disconnected table containg a column of all the periods to show at different granularity (quarter, year, all years) and reference that in a measure. You may create this table manually or with DAX or M.

Please see the sample pbix.

[johnt75]

One option would be to create a calculation group with calculation items for each period or total you want to show. Each item would call SELECTEDMEASURE while applying the appropriate filters to the date table, e.g.

FY24 =
CALCULATE ( SELECTEDMEASURE (), 'Date'[Financial Year] = 'FY24' )

[FBergamaschi]

Please include, in a usable format, not an image, a small set of rows for each of the tables involved in your request and show the data model in a picture, so that we can import the tables in Power BI and reproduce the data model. The subset of rows you provide, even is just a subset of the original tables, must cover your issue or question completely. Do not include sensitive information and do not include anything that is unrelated to the issue or question. Please show the expected outcome based on the sample data you provided and make sure, in case you show a Power BI visual, to clarify the columns used in the grouping sections of the visual.

Need help uploading data? click here

Want faster answers? click here