Adding a Total Row for a Union of Two Tables Excluding the numbers from the second table

[CaveOfWonders]

Hi All,

I created a Union of two tables with the code below which is working fine:

UNION (
    SUMMARIZECOLUMNS (
        'Table'[Category],
        "Measure1", 'Measures_'[m_Measure1],
        "Measure2", 'Measures_'[m_Measure2],
        "Measure3", 'Measures_'[m_Measure3],
        "Measure4", 'Measures_'[m_Measure4],
        "Measure5", 'Measures_'[m_Measure5],
        "Measure6", 'Measures_'[m_Measure6],
        "Measure7", 'Measures_'[m_Measure7],
        "Measure8", 'Measures_'[m_Measure8],
        "Measure9", 'Measures_'[m_Measure9]
    ),
    SUMMARIZECOLUMNS (
        
        "Category", IGNORE ( 'Measures_'[m_Category] ),
        "Measure1", IGNORE ( 'Measures_'[m_Measure1],
        "Measure2", IGNORE ( 'Measures_'[m_Measure2],
        "Measure3", IGNORE ( 'Measures_'[m_Measure3],
        "Measure4", IGNORE ( 'Measures_'[m_Measure4],
        "Measure5", IGNORE ( 'Measures_'[m_Measure5],
        "Measure6", IGNORE ( 'Measures_'[m_Measure6],
        "Measure7", IGNORE ( 'Measures_'[m_Measure7],
        "Measure8", IGNORE ( 'Measures_'[m_Measure8],
        "Measure9", IGNORE ( 'Measures_'[m_Measure9]
    )
)

Output

Category	Measure1	Measure2	Measure3	Measure4	Measure5	Measure6	Measure7	Measure8	Measure9
A	2	2	2	2	2	2	2	2	2
B	2	2	2	2	2	2	2	2	2
C	2	2	2	2	2	2	2	2	2
D	2	2	2	2	2	2	2	2	2
E	2	2	2	2	2	2	2	2	2
F	2	2	2	2	2	2	2	2	2
G	2	2	2	2	2	2	2	2	2
H	2	2	2	2	2	2	2	2	2
I	2	2	2	2	2	2	2	2	2
J	2	2	2	2	2	2	2	2	2
K	10	10	10	10	10	10	10	10	10

The issue I'm having is; how do I add a total row ignoring category K which is the output of the second table.

Note: The Category column from the first table is from an actual table, whereas in the second table it is a measure with "K" in it.

Desired Output

Category	Measure1	Measure2	Measure3	Measure4	Measure5	Measure6	Measure7	Measure8	Measure9
A	3	3	3	3	3	3	3	3	3
B	3	3	3	3	3	3	3	3	3
C	3	3	3	3	3	3	3	3	3
D	3	3	3	3	3	3	3	3	3
E	3	3	3	3	3	3	3	3	3
F	3	3	3	3	3	3	3	3	3
G	3	3	3	3	3	3	3	3	3
H	3	3	3	3	3	3	3	3	3
I	3	3	3	3	3	3	3	3	3
J	3	3	3	3	3	3	3	3	3
K	10	10	10	10	10	10	10	10	10
Total	30	30	30	30	30	30	30	30	30

[v-yingjl]

Hi @CaveOfWonders ,

Not certain what is your measures and category column look like based on your description.

I have tried to create a sample table following your measures but seems like get different result, see the picture and sample file below:

Maybe you can consider sharing the measures and the category column for further discussion.

Best Regards,

Yingjie Li

[CaveOfWonders · Accepted solution]

Hi Yingjie Li,

Thank you for looking into this; I actually managed to resolve this but haven't had the chance to update the post.

The code below solved my problem; essentially, all I had to do was duplicate the first table in the union and add it as a third union, I then replaced 'Table'[Category] with "Category", FORMAT("Category", "ABC") which then returns one row with the aggregated measures.

UNION (
    SUMMARIZECOLUMNS (
        'Table'[Category],
        "Measure1", 'Measures_'[m_Measure1],
        "Measure2", 'Measures_'[m_Measure2],
        "Measure3", 'Measures_'[m_Measure3],
        "Measure4", 'Measures_'[m_Measure4],
        "Measure5", 'Measures_'[m_Measure4],
        "Measure6", 'Measures_'[m_Measure4],
        "Measure7", 'Measures_'[m_Measure4],
        "Measure8", 'Measures_'[m_Measure4],
        "Measure9", 'Measures_'[m_Measure4]
    ),
    SUMMARIZECOLUMNS (
        
        "Category", IGNORE ( 'Measures_'[m_Category] ),
        "Measure1", IGNORE ( 'Measures_'[m_Measure1],
        "Measure2", IGNORE ( 'Measures_'[m_Measure2],
        "Measure3", IGNORE ( 'Measures_'[m_Measure3],
        "Measure4", IGNORE ( 'Measures_'[m_Measure4],
        "Measure5", IGNORE ( 'Measures_'[m_Measure4],
        "Measure6", IGNORE ( 'Measures_'[m_Measure4],
        "Measure7", IGNORE ( 'Measures_'[m_Measure4],
        "Measure8", IGNORE ( 'Measures_'[m_Measure4],
        "Measure9", IGNORE ( 'Measures_'[m_Measure4]
    ),
    SUMMARIZECOLUMNS (
        "Category", FORMAT("Category", "ABC")
        "Measure1", 'Measures_'[m_Measure1],
        "Measure2", 'Measures_'[m_Measure2],
        "Measure3", 'Measures_'[m_Measure3],
        "Measure4", 'Measures_'[m_Measure4],
        "Measure5", 'Measures_'[m_Measure4],
        "Measure6", 'Measures_'[m_Measure4],
        "Measure7", 'Measures_'[m_Measure4],
        "Measure8", 'Measures_'[m_Measure4],
        "Measure9", 'Measures_'[m_Measure4]
    )
)

[vanessafvg]

are you still wanting to see category k row but not sum it in the total?

ie measure = calculate(sum(measure), category <> "k")

[CaveOfWonders]

Hi vanessafvg,

Thank you for replying.

Yes, i want to display the output in a Table visualisation. I need the output of both tables, however, I want to exclude the second table which is just one row (category K) from the total row.