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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
AnGorSan
Frequent Visitor

How to display YTD as a row total

Hello everyone,

 

I have a requirement which would be very simple in SSRS, yet I have no idea how to implement it in Power BI.

Customer wants this kind of table/matrix output:

 

Account Number | Month 1 | Month 2 | ... | Month N| YTD

1234                    value 1      Value 2     ...   Value N  | Value YTD

 

Months are selectable, and can be any from Jan to Dec within a single year.

There are measures for Value and Value YTD in the tabular model I am using.

 

Please help with how to implement this !

Thanks in advance!

Andrey

4 REPLIES 4
v-piga-msft
Resident Rockstar
Resident Rockstar

Hi @AnGorSan ,

 

It seems that your desired output would be achieved with table / matrix visual in Power BI.

 

Are Month1, Month2 ... MonthN from one single column?  

 

If you could share your data sample as table format so that I could understand your scenario better and get the solution quickly.

 

Best Regards,

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi Cherry!

My source data is a Tabular model with transactions fact and Date, Cost Center and Account dimensions.

I have a measure for Transaction Amount, and a measure for Transaction amount YTD.

If I place these two measures into a matrix, the result is not what I am looking for:

Screenshot_1.png

The month value is coming from the single column in my date dimension.

 

For the Table visual, I will have to create a measure per month, and the structure would become rigid, as if the measure has no value, the column will still remain.

Hi @AnGorSan ,

 

If your desired output is like below.

 

matirx.PNG

I'm afraid that we cannot achieve that directly for matrix.

 

You need to create the two matrix like below and turn off the word warp of row header and column header, then hide the account number for the YTD matrix and then pin the two tables as a workaround.

 

Capture.PNG

 

Please have a reference of my attachment.

 

Best Regards,

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi Cherry,

 

Well, it seems that it is as close as you can get, but still, when the number of month is less then 12, the result looks like this:

 

Screenshot_2.png

 

I believe in this case the table visual with per month measures approach is best, as at least the structure would remain intact.

I will try to convince my customer to adjust the requirement to allow this.

 

Thank you for your effort!

Andrey

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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