DAX: Calculate the ratio of Metric A to a sibling Metric B (when both reside in the same group)

Question

I am a complete noob to DAX and OLAP, so please, forgive any errors.

Story

Basically, I have a Matrix component in PowerBI Desktop report. Months are on the columns, Metrics - on the rows:

The problem arises when I try to calculate the ratio of (percents in red):

1. Metric B to Metric A (B/A)
2. Metric C to Metric B (C/B)

All metrics reside in the same group. I wish these ratios be put as a separate, but one column.

I have no problems to calculate Metric ratio to total; I just take SUM(#) and divide by measure Total:

Total = CALCULATE(SUM('Data'[#]); ALL('Data'[Metric]))

Question

But how do I get a SUM(#) aggregate for Metric X which I could later use to calculate the ratio of other metrics?

What I've done so far

As one of the steps I created this table to get the aggregated # for Metric A:

Table = ADDCOLUMNS(
            SUMMARIZE('Data'; 
                      'Data'[GroupAndMetric];
                      'Data'[Year];
                      'Data'[MonthNo]);
            "Count";
            CALCULATE(SUM('Data'[#]); 'Data'[GroupAndMetric] = "Group A - Metric A")
)

Any help appreciated.

Answer 1

I think there is an easier way to get your expected result but I am assuming your data is exactly the same as the posted in the OP.

Create the following measures to calculate the previous and current values in the given context of each metric.

Sum of [#]:

CurrentValue = SUM(Data[#])

Current Group

CurrentGroup = FIRSTNONBLANK(Data[Group],0)

Current Metric

CurrentMetric = FIRSTNONBLANK(Data[Metric],0)

Current Month:

CurrentMonth = FIRSTNONBLANK(Data[Month],0)

This calculates the previous Metric

PrevMetric =
IF (
    [CurrentMetric] = "Metric A",
    BLANK (),
    IF (
        [CurrentMetric] = "Metric B",
        "Metric A",
        IF ( [CurrentMetric] = "Metric C", "Metric B", BLANK () )
    )
)

Calculate the previous metric sum of # necessary for the ratio calculation:

Previous # Sum =
CALCULATE (
    SUM ( Data[#] ),
    FILTER (
        ALL ( Data),
        COUNTROWS (
            FILTER (
                Data,
                EARLIER ( Data[Metric] ) = [PrevMetric]
                    && EARLIER ( Data[Group] ) = [CurrentGroup]
                    && EARLIER ( Data[Month] ) = [CurrentMonth]
            )
        )
    )
)

Finally calculate the ratio:

% Ratio = DIVIDE([CurrentValue], [Previous # Sum])

You will get a bunch of measures but you just have to use the % Ratio in the matrix:

Let me know if this helps.