Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
IHeardPeopleSay
Frequent Visitor

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

Hi,

 

I have the following three tables:

 

Clients TableClients Table

Calendar TableCalendar Table

Budget TableBudget 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 visualMatrix 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.

2 REPLIES 2
v-frfei-msft
Community Support
Community Support

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 )
        )
    )
Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

@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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.