cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
saddas
Frequent Visitor

How to Create a Chart from a Subtotal Column in a Matrix Visual

As shown below, I have a table (Table 1) in long format with three columns: Period (from 1 to 3), FS_Item (Revenue, COGS, OPEX, Assets), and Amount (numerical). From that table, I can create a Matrix visual (similar to second image below) that filters out one value from FS_Item (Asset) and sums up the remaining three values to come up with a Total column (Revenue - COGS - OPEX). 

I would like to create a Chart that plots Period and Total (the yellow highlighted columns). I assume that I would first need to create a table that is identical to my Matrix visual. 

Any help would be greatly appreciated. Thanks.

 

.Screenshot1Screenshot1

 

Screenshot2Screenshot2

1 ACCEPTED SOLUTION
themistoklis
Super User
Super User

@saddas 

 

Simply create a new measure with the following formula:

Amount New = CALCULATE(SUM(Sheet1[Amount]), FILTER(Sheet1, Sheet1[FS_Item] IN {"Revenue", "COGS", "OPEX"}))

 

And then create the table orgraph based on Period and Amount New fields

 

See attached file:

View solution in original post

2 REPLIES 2
saddas
Frequent Visitor

Great, thanks very much. I had figured out a longer formula (see below), but yours is much more efficient.

Long version of formula:

Amount New = CALCULATE(SUM(Sheet1[Amount]),Sheet1[FS_Item]="Revenue") + CALCULATE(SUM(Sheet1[Amount]),Sheet1[FS_Item]="COGS") + CALCULATE(SUM(Sheet1[Amount]),Sheet1[FS_Item]="OPEX")

themistoklis
Super User
Super User

@saddas 

 

Simply create a new measure with the following formula:

Amount New = CALCULATE(SUM(Sheet1[Amount]), FILTER(Sheet1, Sheet1[FS_Item] IN {"Revenue", "COGS", "OPEX"}))

 

And then create the table orgraph based on Period and Amount New fields

 

See attached file:

View solution in original post

Helpful resources

Announcements
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors