Why are my totals not being displayed in Matrix table

[mark_carlisle]

I've looked at several similar posts but none seem to resolve my issue.

I have setup a matrix table that uses calculation groups. The Name field in the Rows of the matrix table is a calculation group (CGRP_OpenCloseBalance). If we take the Add calculation item as an example the DAX for this is...

Expression = 
SWITCH (
    SELECTEDVALUE ( CGRP_BookingMetric1[Metric] ),
    "Units",
        CALCULATE (
            SELECTEDMEASURE (),
            FILTER (
                FACT_Bookings,
                FACT_Bookings[GTM Transaction type] = "New"
                    || FACT_Bookings[GTM Transaction type] = "Expansion - Cross Sell"
                    || FACT_Bookings[GTM Transaction type] = "Migration"
                    || FACT_Bookings[GTM Transaction type] = "Reactivation"
            )
        ),
    "ACV (Booked)",
        CALCULATE (
            SELECTEDMEASURE (),
            FILTER (
                FACT_Bookings,
                FACT_Bookings[GTM Transaction type] <> "Cancelled Subscription"
            )
        )
)

Format String Expression =
SWITCH (
    SELECTEDVALUE ( CGRP_BookingMetric1[Metric] ),
    "Units",
        IF (
            ISSELECTEDMEASURE ( [Booking Metric 1] ),
            "#,0",
            SELECTEDMEASUREFORMATSTRING ()
        ),
    "ACV (Booked)",
        IF (
            ISSELECTEDMEASURE ( [Booking Metric 1] ),
            "#,0.00",
            SELECTEDMEASUREFORMATSTRING ()
        )
)

The Metric Slicer is a calculation group (CGRP_BookingMetric1) that allows the end user to pick the metric that they want to view in the matrix. If we take the two calculation items, Units and ARR (Booked) from the DAX above as the examples the DAX for those...

---
UNITS
---

Expression = 
IF (
    ISSELECTEDMEASURE ( [Booking Metric 1] ),
    [Booked Volume],
    SELECTEDMEASURE ()
)

Format String Expression = 
IF (
    ISSELECTEDMEASURE ( [Booking Metric 1] ),
    "#,0",
    SELECTEDMEASUREFORMATSTRING ()
)

---
ACV (Booked)
---

Expression = 
IF (
    ISSELECTEDMEASURE ( [Booking Metric 1] ),
    [ACV (Booked)],
    SELECTEDMEASURE ()
)

Format String Expression = 
IF (
    ISSELECTEDMEASURE ( [Booking Metric 1] ),
    "#,0.00",
    SELECTEDMEASUREFORMATSTRING ()
)

The Booking Metric 1 is a measure and the DAX for this is..

Booking Metric 1 =
0

I've tried switching the precedence of these calculation groups in case of an incorrect ordering there but everything seems to be setup correctly but is obviously not so what am I missing? Is this something that is simply not possible with calculation groups?

[mark_carlisle]

Done a bit more experimenting and I've found that by transposing the Rows and Columns that I get totals as expected.

Visually though this is not what I want to achieve. So I my question now is, is possible to sum the seperate calculation items (Add and Churn) in the visual when they are Rows and not Columns?

I'm guessing the answer is a no with a workaround being to create a Total calculation item that does this.

[v-xiaotang]

Hi @mark_carlisle 

you can turn on this option

Best Regards,

Community Support Team _Tang

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

[mark_carlisle]

No still an issue.

[v-xiaotang]

Hi @mark_carlisle 

looks weird. the difference may be that in my case, the value in the Values is a ∑column?

could you share your sample file after removing sensitive data, and you can replace it with fake data, I'll check your sample file later.

Best Regards,

Community Support Team _Tang

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

[mahoneypat]

You are using SELECTEDVALUE in your SWITCH.  In the total row, there is not a single value and it returns blank.  You can try adding a default expression in your SWITCH at the end, so it generates a result in the total row.

Pat

[mark_carlisle]

Hi Pat,

Alas no that's not the issue. I was experiencing the issue before the SWITCH went in but to double check I've removed it from the calculation items and I get the same issue.