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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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
Community Champion
Community Champion

@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
Community Champion
Community Champion

@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:

Helpful resources

Announcements
March Fabric Community Update

Fabric Community Update - March 2024

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

Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.