Calculate with Measure

[Anonymous]

Hello,
I started using PBI only a few weeks ago and I'm not very familiar with this tool. I got a table 'Prognoseelement' with this content:
ForecastCategoryName   AmountWithoutAdjustments   ForecastAmount
Pipeline                      0                        0
Closed                   23.239                   23.239
Commit                   69.364                   69.364
Best Case               343.751                  343.751
                        436.354                  436.354
                        413.116                  413.116
                         92.603                  350.000

The value 92.603 is the sum of Closed+Commit and I'm calculating it with this measure. This works and results in the correct value 92.603:
Commit FC = CALCULATE(SUM(Prognoseelement[AmountWithoutAdjustments]);FILTER(ALL(Prognoseelement[ForecastCategoryName]);OR(SEARCH("Closed";Prognoseelement[ForecastCategoryName];1;0);SEARCH("Commit";Prognoseelement[ForecastCategoryName];1;0))))

With another measure I'm trying to filter out the value 350.000. Works also fine.
Commit FC Adj = CALCULATE(Sum(Prognoseelement[ForecastAmount]);All(Prognoseelement);FILTER(Prognoseelement;Prognoseelement[ForecastCategoryName]="" && Prognoseelement[AmountWithoutAdjustments]=92.603))

But if I replace 92.603 (changes from month to month) with the 1st measure 'Commit FC' it results in an empty value instead of 350.000. Has anybody an idea what's going wrong?

Best Regards,

Harald

[sturlaws]

Hi @Anonymous 

you can do it like this

Commit FC =
CALCULATE (
    SUM ( Prognoseelement[AmountWithoutAdjustments] );
    FILTER (
        Prognoseelement;
        Prognoseelement[ForecastCategoryName] IN { "Commit"; "Closed" }
    )
)

The second measure can be done like this, I don't recommend it, it would be better to change you datamodell a little so that each row has a value in ForecastCategoryName-column

Commit FC Adj =
VAR cfc = [Commit FC]
RETURN
    CALCULATE (
        SUM ( Prognoseelement[ForecastAmount] );
        FILTER ( Prognoseelement; Prognoseelement[AmountWithoutAdjustments] = cfc )
    )

[Anonymous]

Hi  @sturlaws ,

Thanks a lot for your great and quick support. Your solution works well for single lines, but unfortunately the total values in the bottom line are not calculated, if there are more than one line in my matrix table. There is a additional column with different person, which I didn't add in my examble above.

Best Regards,

Harald

[sturlaws]

I really think your datamodell is causing you some trouble, and will make it complicated for you to get the answers you are after. If you upload a sample of your report, I can have look at it and make some suggestions.

[Anonymous]

Hi @sturlaws ,

Thank you again for your very much appreciated help!!! Here the link to my data  modell:

https://1drv.ms/u/s!AkUKDtUDXRuVhaN9v2mur2-BRHUnmw

Regards,

Harald

[sturlaws · Accepted solution]

Is this how your input data looks like when you get them, or are you doing any prosessing before you read it into power bi?

Having those rows without a forecastingCategoryName is really unpractical, you could consider splitting up the tables, or give each row a forecastingCategoryName before reading it in. But that would depend on what your data source is, and how much data you've got.

If that is not possible, you can create a new forecastingCategoryName-column. In principle the same as was done in the measure before, but now moved to a calculated column:

ForecastCategoryNameNEW =
IF (
    ISBLANK ( Tabelle1[ForecastCategoryName] );
    IF (
        CALCULATE (
            SUM ( Tabelle1[AmountWithoutAdjustments] );
            FILTER (
                Tabelle1;
                Tabelle1[OwnerId] = EARLIER ( Tabelle1[OwnerId] )
                    && Tabelle1[ForecastCategoryName] IN { "Commit"; "Closed" }
            )
        ) = Tabelle1[AmountWithoutAdjustments];
        "Commit+Closed";
        Tabelle1[ForecastCategoryName]
    );
    Tabelle1[ForecastCategoryName]
)

Then we can use this new column in the Commit FC Adj-measure

Commit FC Adj =
CALCULATE (
    SUM ( Tabelle1[AmountWithoutManagerAdjustment] );
    FILTER ( Tabelle1; Tabelle1[ForecastCategoryNameNEW] = "Commit+Closed" )
)

[Anonymous]

Hi @sturlaws ,

The uploaded file contains a part of my original source, which has more than 500k records. It is unchanged and there is no way for me to derive the ForecastCategoryName from any linked table 😞

But your proposal with an added column looks good and I will use it. Thanks again for your amazing help!!!

Regards,

Harald