cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Helper IV

## Edit Sub totals using dax

Hi ,

Basically below is the output that i require to show , which is sales of countries for  current fiscal yeat Month wise.

 Region June'23 July'23 Aug'23 Sep'23 Oct'23 Nov'23 Dec'23 Jan'24 Feb'24 Mar'24 Apr'24 May'24 Jun'24 FY 24 India 100 111 112 113 114 115 116 117 118 119 120 121 122 1398 Srilanka 101 112 113 114 115 116 117 118 119 120 121 122 123 1410 Malaysia 102 113 114 115 116 117 118 119 120 121 122 123 124 1422 singapore 103 114 115 116 117 118 119 120 121 122 123 124 125 1434

FY 24 column below is just the subtotals i have enabled and renamed it.  Logically for FY 24 i should calculate sume of ( July'23- June 24) but since i have a column for June'23 , my totals is considering Jun'23 also which is not correct.

Also my calendar is a rolling calendar , it will consider , current month and next 11 months , so i could have the values coming for Current fiscal and previous fiscal , also current fiscal and Next fiscal . But ultimately i need to show the totals of current fiscal year only .

For Example : For India , FY 24 is showing 1398 , ideally it should show 1298 since June'23 is not FY 24. Since this is a matrix visual i cannot create a separate measure and drag to values as i will have repeating value for each month separately. Is there any way we can achieve this, Please help.

1 ACCEPTED SOLUTION
Community Support

Hi ,  @binayjethwa

According to your description, you want to add a Sub total as the column in your matrix visual.

For your needs, you need to recreate a dimension table for the Column field, such as:

Table  =UNION( VALUES('Table'[Year_month]) , "FY 24")
Sure , you can also add a rank column to sort the column field.

Then we can use this Table's column as the Matrix visual's column field.

Then we need to create a measure , use MAX('Table'[Column]) to get the value of the current column header, and then use the IF() and  return the corresponding value according to different situations to meet your needs.

Secondly, according to your description, I don't really understand your company's fiscal year policy, and I don't have your test data, it is difficult to write dax according to different screening contexts, (July'23- June 24) refers to July every year To the value of June, if it is currently July'24, that is the value calculation (Aug'23-July'24)?And the July'23 means the 2023/7/1 ; June 24 means the 2024/6/1?

However, these are all implemented in the measurement value according to your business needs. For commonly used functions, you can consider using EDATE, EOMONTH(), FILTER(), SUMX() to achieve your needs.

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI!

Best Regards,

Aniya Zhang

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

2 REPLIES 2
Super User

Hi,

Not sure how much i can help but i can try.  Share the download link of the PBI file.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Community Support

Hi ,  @binayjethwa

According to your description, you want to add a Sub total as the column in your matrix visual.

For your needs, you need to recreate a dimension table for the Column field, such as:

Table  =UNION( VALUES('Table'[Year_month]) , "FY 24")
Sure , you can also add a rank column to sort the column field.

Then we can use this Table's column as the Matrix visual's column field.

Then we need to create a measure , use MAX('Table'[Column]) to get the value of the current column header, and then use the IF() and  return the corresponding value according to different situations to meet your needs.

Secondly, according to your description, I don't really understand your company's fiscal year policy, and I don't have your test data, it is difficult to write dax according to different screening contexts, (July'23- June 24) refers to July every year To the value of June, if it is currently July'24, that is the value calculation (Aug'23-July'24)?And the July'23 means the 2023/7/1 ; June 24 means the 2024/6/1?

However, these are all implemented in the measurement value according to your business needs. For commonly used functions, you can consider using EDATE, EOMONTH(), FILTER(), SUMX() to achieve your needs.

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI!

Best Regards,

Aniya Zhang

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

Announcements

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors