Replicating a value on each row (can't use LOOKUPVALUE since I need a sum)

[IHeardPeopleSay]

Hi,

I have the following three tables:

Clients Table

Calendar Table

Budget Table

ClientUK is a calculated column, it's just a simple concatenation of the ClientID and Company columns.

Do keep in mind that while a client can be in more than one company, it will always have the same BU regardless.

The budget value is always on the last day of the month, what I need is for that budget to be replicated on each day, so that I can later work with it to make a weighted average.

The problem I have is that I can either choose with only one company at the time:

BDG = 
VAR SearchDate = EOMONTH(SELECTEDVALUE(Calendar[Date]); 0)
VAR SearchBU = SELECTEDVALUE(Clients[BU])
VAR SearchCompany = SELECTEDVALUE(Clients[Company])

RETURN
    CALCULATE (
        SUM ( Budget[ValueBDG] );
        FILTER (
            ALLNOBLANKROW ( Calendar[Date] );
            Calendar[Date] = SearchDate
        );
        FILTER (
            ALLNOBLANKROW ( Clients[BU] );
            Clients[BU] = SearchBU
        );
        FILTER (
            ALLNOBLANKROW ( Clients[Company] );
            Clients[Company] = SearchCompany
        );
        ALL ( Budget )
    )

Or have a sum of all three that disregards any company filter:

BDG = 
VAR _SearchDate = EOMONTH(SELECTEDVALUE(Calendar[Date]); 0)
VAR _SearchBU = SELECTEDVALUE(Clients[BU])
--VAR _SearchCompany = SELECTEDVALUE(Clients[Company])

--VAR _FilterCompany = FILTER ( ALLNOBLANKROW ( Clients[Company] ); Clients[Company] = _SearchCompany )

RETURN
    CALCULATE (
        SUM ( Budget[ValueBDG] );
        FILTER (
            ALLNOBLANKROW ( Calendar[Date] );
            Calendar[Date] = _SearchDate
        );
        FILTER (
            ALLNOBLANKROW ( Clients[BU] );
            Clients[BU] = _SearchBU
        );
        --IF(_SearchCompany = BLANK(); ALL(Clients[Company]); _FilterCompany);
        ALL ( Budget )
    )

It's basically the same but without the company stuff; the commented lines are when I tried to make the filter dynamic, based on the value of _SearchCompany, but it gives me the error "True/False expression without a specific column." (Changing ALL(Clients[Company]) to ALL(Clients) also gives an error)

Here's a screenshot of the result too:

Matrix visual

I can't use LOOKUPVALUE since it's just a look up for one single value, and I need a sum for when multiple or all companies are selected.

Here's the link to the .pbix I prepared, it's not the original since it has sensitive data, but I made the replica as similar as possible and of course it has the same problem:

https://send.firefox.com/download/c47d098d71515f74/#Z2kBIp56uGl-Xlu3XVkBwA

I did wonder if I'm just needlessly complicating things while writing this, but oh well.

[v-frfei-msft]

Hi @IHeardPeopleSay ,

Is that you want?

BDG =
VAR _SearchDate =
    EOMONTH ( SELECTEDVALUE ( Calendar[Date] ), 0 )
VAR _SearchBU =
    SELECTEDVALUE ( Clients[BU] )
VAR _SearchCompany =
    SELECTEDVALUE ( Clients[Company] )
VAR _FilterCompany =
    FILTER ( ALLNOBLANKROW ( Clients[Company] ), Clients[Company] = _SearchCompany )
RETURN
    IF (
        _SearchCompany = BLANK (),
        CALCULATE (
            SUM ( Budget[ValueBDG] ),
            FILTER ( ALLNOBLANKROW ( Calendar[Date] ), Calendar[Date] = _SearchDate ),
            FILTER ( ALLNOBLANKROW ( Clients[BU] ), Clients[BU] = _SearchBU ),
            ALL ( Clients[Company] ),
            ALL ( Budget )
        ),
        CALCULATE (
            SUM ( Budget[ValueBDG] ),
            FILTER ( ALLNOBLANKROW ( Calendar[Date] ), Calendar[Date] = _SearchDate ),
            FILTER ( ALLNOBLANKROW ( Clients[BU] ), Clients[BU] = _SearchBU ),
            KEEPFILTERS ( _FilterCompany ),
            ALL ( Budget )
        )
    )

[IHeardPeopleSay]

@v-frfei-msft

While it appears to be working, it actually isn't because the value is correct when you either select one or all companies, but if you select only two out of three (talking about the dummy .pbix I provided, since I have more in the data I can't show) then the measure will still give you the sum of all companies' budget.

I also didn't want to write the formula like that if possible since I don't know if it's just the company filter giving me problems, so if I had to write it like that for each combination of filters, it'd be a nightmare, if not impossible.

If I really have to write it like that, I'd have to separate the dataset I'm currently using to have it extract fewer columns, which would mean less detail.