topic Re: Create Measure or calculated Column for tabular Total in DAX Commands and Tips

Create Measure or calculated Column for tabular Total

009co — Sun, 14 Aug 2022 06:45:00 GMT

Is it possible to create a Measure or calculated Column that has a tabular Total?

If I create a Power BI table viz and enable Totals for that table, the Total is what I want as a as if it were an independent grouping that I can use in a viz or calculations.

The attached Excel mock up screenshot attached here illustrates what I am trying to get.

Any hints tips etc welcome and appreciated!

Re: Create Measure or calculated Column for tabular Total

daXtreme — Sun, 14 Aug 2022 08:05:33 GMT

[Total Across Visible Categories] = CALCULATE( [Your Measure], ALLSELECTED( T[Category] ) )

Re: Create Measure or calculated Column for tabular Total

009co — Sun, 14 Aug 2022 08:52:08 GMT

Thanks. That is interesting. But not what I am looking for.

Using the new measure you have lets me created the following yellow columns.

But I would like to add another category value that is the "Category Total" green row.

Re: Create Measure or calculated Column for tabular Total

daXtreme — Sun, 14 Aug 2022 10:18:55 GMT

009co

What you're asking for is not so easy to create. Alberto Ferrari has a vid on YT that deals with such issues. Basically, you have to create a different table with this row 'Category Total' added, connect this table to other table(s) and create a measure that will do the right thing. It's not basic stuff. If I find this video, I'll come back and give you a link. But you could try to locate it yourself on YT. Just try to search for phrases like "add total row", "add others"...

Here's something to get you started: Filtering the top products alongside the other products in Power BI - SQLBI

Re: Create Measure or calculated Column for tabular Total

009co — Sun, 14 Aug 2022 11:27:10 GMT

Ok yes this is not a DAX solution but need to do it at the query level.

Something like:

1. Make another query that aggregate category values as new category column value named "Category Total".

2. Append new query to original query. Query results will then have rows for each category, and a new row for category total.

Have to ensure that the new category "category total" is only used where relevant.