Column subtotal of my matrix not being summed properly

[RogerSteinberg]

Hi,

I have this measure which gives me the ratio of revenue to cost. My table has several dimensions such as date, country, product and so on.

My matrix has the following fields:

Rows:

Start Date, Total Cost

Columns:

Revenue Month

Values:

Formula

Formula = 
var num = 
SUMX(
    'Fact',
    'Fact'[Revenue]
)
VAR den = 
SUMX(
    'Fact',
    'Fact'[Actual Cost]
)
RETURN
DIVIDE(
    num,
    den
)

 My values are good. My issue lies in the Column subtotal of my matrix. I would like that column to sum the entire row values. Instead, it seems to be aggregating the revenue column and dividing it by the cost column for the entire grouping which is not what I want.

My intended result should look like this

Start Date	Cost	1-Jan	1-Feb	1-Mar	1-Apr	1-May	Total
1-Jan	$   10.00	20%	30%	40%	50%	60%	200%
1-Feb	$   20.00		20%	30%	45%	60%	155%
1-Mar	$   25.00			32%	45%	60%	137%

As you can see it is summing all the % for its respective row to give the column subtotal

Instead it is doing something like this:

Start Date	Cost	1-Jan	1-Feb	1-Mar	1-Apr	1-May	Total
1-Jan	$   10.00	20%	30%	40%	50%	60%	45%
1-Feb	$   20.00		20%	30%	45%	60%	40%
1-Mar	$   25.00			32%	45%	60%	35%

[pranit828]

HI @RogerSteinberg 

On the values section

Is Sum selected for the column'Total'

If the above approach doesn't work then I would create a measure as below.

_Total = 
     CALCULATE(SUMX('Fact','Fact'[Formula]), ALLEXCEPT('Fact','Fact'[Start Date],'Fact'[Cost]))

[RogerSteinberg]

It can only be a measure.

Unfortunately the allexcept suggestion doesn't work. It does the opposite. The values are not matching but the subtotal works.

[v-kelly-msft]

Hi  @RogerSteinberg ,

Can you upload your .pbix file to onedrive business and share the link with us?

Remember to remove the confidential information.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[RogerSteinberg]

Here's raw data:

Start	Product	Cost	Revenue	Revenue Month
1-Jan	A	10	20	1-Jan
1-Jan	A	10	30	1-Feb
1-Jan	A	10	40	1-Mar
1-Feb	A	20	25	1-Feb
1-Feb	A	20	30	1-Mar
1-Feb	A	20	35	1-Apr
1-Jan	B	25	25	1-Jan
1-Jan	B	25	40	1-Feb
1-Jan	B	25	70	1-Mar
1-Feb	B	50	60	1-Feb
1-Feb	B	50	70	1-Mar
1-Feb	B	50	90	1-Apr

And the Calculated column for the cost by Month :

TotalCost by YearMonth = 
CALCULATE(
    SUMX( 
        VALUES('Fact'[Product] ), 
        CALCULATE(AVERAGE('Fact'[Cost])) 
        ),
    ALLEXCEPT(
        'Fact',
        'Fact'[Start]
        )
)

[v-kelly-msft]

Hi  @RogerSteinberg ,

Create 2 measures as below:

_Average  = CALCULATE(AVERAGE('Fact'[Cost]),FILTER(ALL('Fact'),'Fact'[Product]=MAX('Fact'[Product])&&'Fact'[Start]=MAX('Fact'[Start])))

_Total = SUMX(VALUES('Fact'[Revenue Month]),'Fact'[_Average])&"%"

And you will see:

For the related .pbix file,pls click here.

I think your formular should be modified as below:

TotalCost by YearMonth = 
CALCULATE(
    SUMX( 
        VALUES('Fact'[Revenue Month] ), 
        CALCULATE(AVERAGE('Fact'[Cost])) 
        ),
    ALLEXCEPT(
        'Fact',
        'Fact'[Start]
        )
)

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[RogerSteinberg]

Two things.

For the first measure im not sure why you use cost. Please see my initial question which explains how the values are to be aggregated. I've also included the intended result below.

The formula for the values should be the ratio of Revenue to Cost. In other words, sum the revenue for the revenue month associated to the start month divided by the cost for that cohort.

This gives me the right values, but the aggregation doesn't work for my Column Subtotals

DIVIDE(
    SUMX(
        'Fact',
        'Fact'[Revenue]
    )
    ,
    SUMX(
        'Fact',
        'Fact'[Cost]
    ))

Second, my formula for TotalCost by Month is correct. No need to change it. This formula is the aggregation of cost grouped by start month and revenue month. It removes duplicates

Here is the intended result

Start	Cost	1-Jan	1-Feb	1-Mar	1-Apr	Totals
1-Jan	35	129%	200%	314%		643%
1-Feb	70		121%	157%	179%	457%

For the value 1-Jan for row Jan 1 which is 129 % it is calculated as such:

(20+25)/35 = 1.29 = 129%

For the value 1-Feb for row Jan1 which is 200% it is calculated as such:

(30+40)/35 =2 = 200%

[v-kelly-msft · Accepted solution]

Hi @RogerSteinberg ，

I just wanna show you a method about calculating the total value in row,not meant to modify your expression.

You can create 2 measures as below:

_Formula = 
var num = 
SUMX(
    FILTER('Fact','Fact'[Revenue Month]=MAX('Fact'[Revenue Month])),
    'Fact'[Revenue]
)
VAR den = 
MAX('Fact'[TotalCost by YearMonth])
RETURN
DIVIDE(
    num,
    den
)

_Total = SUMX(VALUES('Fact'[Revenue Month]),'Fact'[_Formula])

And you will see:

For the related .pbix file,pls click here.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!