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

## Add a Fix Column to 'Columns subtotal' in Matrix.

Hi Experts,

I have a matrix to show the inventory by three diferent Categories:

• Uniforms
• Beverages
• Food

That amounts are calculated and Vary by month.

The user can select from the Slicer 1,2 or all categories and the Matrix show a total by Categories selected without issues.

The results is similar to this if all categories are selected.

 Restaurant Beverages Food Uniforms Total Rst-01 250 300 1200 1750 Rst-02 500 350 860 1710 Rst-03 600 450 750 1800 Rst-04 800 600 700 2100 Rst-05 1200 167 800 2167

Now, the user wants show in the matrix two additional columns(Initial Setup and Other setup) with Fixed values by Restaurant, but he wants to add the fixed amounts in the 'total'

NOTE: that amounts  for Initial Setup and Other setup are fixed By restaurant (no calculations)

By Eaxmple:

If the user ONLY select Beverages and Food, the TOTAL must sum Beverages and food PLUS Initial setup and Other setup.

I added the new columns in the row fields and it are showed in the Matrix.

 Restaurant Initial setup Other Setup Beverages Food Total Rst-01 100 50 250 300 700 Rst-02 120 150 500 350 1120 Rst-03 300 120 600 450 1470 Rst-04 256 80 800 600 1736 Rst-05 213 50 1200 167 1630

How can I do that, if in the categories I only have the three categories that I already mentioned

In other words i need that the last colum (Total) summarize the amounts in categories selected Plus the two fixed values(Initial Setup and Other setup). Is it Possible?

1 ACCEPTED SOLUTION
Community Support

Hi @gomezc73 ,

1.Unpivot all categories column.

``````Column =
'Table'[Initial setup] + 'Table'[Other Setup]
``````

``````total =

CALCULATE (

SUM ( 'Table'[Value] ),

FILTER ( 'Table', 'Table'[Restaurant] = SELECTEDVALUE ( 'Table'[Restaurant] ) )

)

+ SELECTEDVALUE ( 'Table'[Column] )``````

Output:

If it does not help, please provide more details with your desired out put and pbix file without privacy information.

Best Regards,

Yifan Wang

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

Community Support

Hi @gomezc73 ,

1.Unpivot all categories column.

``````Column =
'Table'[Initial setup] + 'Table'[Other Setup]
``````

``````total =

CALCULATE (

SUM ( 'Table'[Value] ),

FILTER ( 'Table', 'Table'[Restaurant] = SELECTEDVALUE ( 'Table'[Restaurant] ) )

)

+ SELECTEDVALUE ( 'Table'[Column] )``````

Output:

If it does not help, please provide more details with your desired out put and pbix file without privacy information.

Best Regards,

Yifan Wang

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