Subtract multiple columns in power bi matrix from a base column in same matrix

Question

I have a matrix with portfolios as columns and portfolio total as values. I want to subtract each portfolio by a base portfolio. The below code returns the portfolio value in each column just fine. But when I try to hold the base portfolio constant I get the following error and the column returns nothing.

Relative Exposure = 
VAR Portfolio = 
   SUMX(FILTER(Portfolio,Portfolio[VALUE]<>"#N/A"),[Weight] * Portfolio [VALUE])

VAR Base_Portfolio = 
        CALCULATE(SUMX(FILTER(ALLEXCEPT(Portfolio,Portfolio[Portfolio]),Portfolio[Portfolio]="Base_Portfolio" &&Portfolio[VALUE]<>"#N/A"),[Weight] * Portfolio[VALUE]))

RETURN Base_Portfolio - Portfolio

"A single value for column 'Portfolio' in table 'Portfolio' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result."

Example Portfolio and Exposure

I don't see 'FoAccountSymbol' or 'PortfolioHoldings_Analytics' in your measure at all. Does it have something to do with [Weight]? — Ryan B.
I removed AccountSymbol and replaced with Portfolio for confidentiality purposes. — Evan DuKate
I added an example table of Portfolios 1,2,3...etc and the exposure. I want to subtract portfolios 2,3,4,5...etc from portfolio 1. — Evan DuKate

Ryan B. Ryan B. · Accepted Answer · 2020-02-06T17:55:55

A few things to fix:

1) In one section you apply a text filter: Portfolio[VALUE]<>"#N/A". But in another place you use the same field in a calculation: [Weight] * Portfolio [VALUE]. This is going to cause an error, it can't be both text and a number.

2) You have a table named Portfolio and a Variable named Portfolio -- that's going to cause an error too.

3) There isn't much point in preserving the filters on Portfolio ... you are about to restrict Portfolio to 'Base_portfolio' in the next statement anyhow. So just remove the 'ALLEXCEPT' -- it's not doing anything for you.

4) By putting your 'base' filter inside the SUMX, you make it so the only row that gets a value will be 'base.' Move that filter out to the Calculate wrapper and you'll get the calculation on every row.

Relative Exposure = 

VAR P1 = 
   SUMX(FILTER(Portfolio,Portfolio[VALUE]<>0), [Weight] * Portfolio [VALUE])

VAR Base_Portfolio = 
    CALCULATE(
        SUMX(FILTER(
                Portfolio
                ,Portfolio[VALUE]<>0
            ), [Weight] * Portfolio[VALUE])
        ,Portfolio[Portfolio Name]="Base_Portfolio"
    )

RETURN Base_Portfolio - P1

Subtract multiple columns in power bi matrix from a base column in same matrix

1 Answers