Power BI: Multiply two columns regardless of filters applied and without summarising

[jeesan1234]

I have two columns 'ExpectedClaimsPerDay' and 'Number of Working Days' as shown in the image example below:

Movement Date.Movement Date (groups)	Sum of Net Earned Premium	Sum of Total NLR	Total Claims	TotalWorkingDaysInMonth	Number of Working Days	ExpectedClaimsPerDay	Expected Claims
September 27th - 1st	120,528.16	0.61	73,051.77	15.00	3.00	4,870.12	379,869.18

In Power BI, I want to multiple the two columns and I tried using the formula:

Expected Claims = ('Expected Claims'[ExpectedClaimsPerDay])*(SUM(Expected[Number of Working Days]))

However, this as you can see in the image is not giving me the correct results as it summing all the 'Number of Working Days'. And if I don't use sum, it is giving me errors as it is expecting a single value. I have tried using distinct but that breaks the visuals as there are multiple 'Movement Date (groups)' selected in the filter.

I want, regardless of the filter applied, whatever is in 'Number of working days' without summing or anything, multiply whatever is in 'ExpectedClaimsPerDay'.

Just to add the above formula works when I use DISTINCT instead of SUM, but it breaks if multiple Movement Group Dates are selected in the filter. It only works if I pick one.

[Kaviraj11]

Try this - 

Expected Claims = SUMX(
    'Expected Claims',
    'Expected Claims'[ExpectedClaimsPerDay] * 'Expected Claims'[Number of Working Days]
)

[jeesan1234]

I tried that but it is giving me an error on 'Number of Working days' saying: 'Cannot find name'. I believe it is expecting some sort of aggregation (which is something I don't want).

[v-rzhou-msft]

Hi @jeesan1234 ,

According to your statement, I think there are at least two tables 'Expected Claims' and 'Expected‘ in your data model.

I think there should be a relationship between 'Expected Claims' (many) and 'Expected‘ (one) tables. 

So when you use SUM() to get number of workday from 'Expected‘, it will return a summarize result.

In my sample, I create relationship between [Movement Date.Movement Date (groups)] columns.

Here I suggest you to try code as below to create a measure. If the related column is not [Movement Date.Movement Date (groups)] you can change the column in _WorkDay code part.

Expected Claims = 
VAR _WorkDay =
    CALCULATE (
        SUM ( Expected[Number of Working Days] ),
        FILTER (
            ALLSELECTED ( Expected ),
            Expected[Movement Date.Movement Date (groups)]
                = MAX ( 'Expected Claims'[Movement Date.Movement Date (groups)] )
        )
    )
RETURN
    SUMX (
        VALUES ( 'Expected Claims'[Movement Date.Movement Date (groups)] ),
        'Expected Claims'[ExpectedClaimsPerDay] * _WorkDay
    )

Result is as below.

You can download my sample file to learn more details.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[jeesan1234]

Thank you for getting back @v-rzhou-msft 

I do have two tables and they are both joined on the month column, however, in table 'Expected', there is a column called Month week so each month is split into different weeks. Therefore, the data from table 'Expected Claims' gets split (repeated) into many. 

I tried the formula above by replacing 'movement groups' with 'month' column but again it is giving me the same number I have shown in my result example.

Just to add 'Movement Date groups' comes from a third table that is connected to the table 'Expected' but not 'Expected Claims'.

[v-rzhou-msft]

Hi @jeesan1234 ,

Please share a sample file with us and show us a screenshot with the result you want. This will make it easier for us to find the solution.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.