Combine multiple rows and show as comma separated and sum up values

[Anonymous]

Hello,

I have a bit challenging requirement as described below. Below is sample of data that I have.

File name	Policy	Start date	End Date	Amt
A	a1	01-01-2020	01-01-2021	100
A	a2	01-01-2020	01-01-2021	200
A	a3	01-01-2020	01-01-2021	300
A	a4	04-04-2020	03-04-2021	400
A	a5	06-03-2019	05-03-2020	500
B	b1	25-05-2019	24-05-2020	600
B	b2	17-06-2020	16-06-2021	700

I have 2 filters - File name and Policy.

When I select File name - "A",  and Policy a1,a2,a3, I should get the below output.

File Name	A
Policy	a1,a2,a3
Amt	600

As you can see from the above output Amt of policies a1,a2,a3 have summed up and total is shown as 600 and three policies have been shown as comma seperated.

But the logic to be applied here is that start date and end date of all the policies should be same, only then summation will happen.In this case a1,a2,a3 had same start and end date. If instead of a1,a2,a3 I select a1,a2,a4 although a1,a2 has same dates but a4 has different date and hence it should show either blank/ or some error message / or some pop up.

So basically, when I select 1 filename and then when I select multiple policies, if start and end date is same for all the policies should get sum of amt and show policies as comma seperated. If any policy has different start and end date, then no values should be shown.

Is it possible to achieve this in Power BI.

Regards,

Amit Darak

[Icey · Accepted solution]

Hi @Anonymous ,

Please check if this could meet your requirements:

Policy Measure = 
VAR t =
    FILTER (
        ADDCOLUMNS (
            'Table',
            "Count_",
                COUNTROWS (
                    FILTER (
                        'Table',
                        'Table'[File name] = EARLIER ( 'Table'[File name] )
                            && 'Table'[Policy] <= EARLIER ( 'Table'[Policy] )
                            && 'Table'[Start date] = EARLIER ( 'Table'[Start date] )
                            && 'Table'[End Date] = EARLIER ( 'Table'[End Date] )
                    )
                ),
            "Countrows_runningtotal",
                COUNTROWS (
                    FILTER (
                        'Table',
                        'Table'[File name] = EARLIER ( 'Table'[File name] )
                            && 'Table'[Policy] <= EARLIER ( 'Table'[Policy] )
                    )
                )
        ),
        [Count_] = [Countrows_runningtotal]
    )
RETURN
    IF (
        COUNTROWS ( t ) = COUNTROWS ( ALLSELECTED ( 'Table'[Policy] ) ),
        CONCATENATEX ( t, [Policy], ", " )
    )

Amt Measure = 
VAR t =
    FILTER (
        ADDCOLUMNS (
            'Table',
            "Count_",
                COUNTROWS (
                    FILTER (
                        'Table',
                        'Table'[File name] = EARLIER ( 'Table'[File name] )
                            && 'Table'[Policy] <= EARLIER ( 'Table'[Policy] )
                            && 'Table'[Start date] = EARLIER ( 'Table'[Start date] )
                            && 'Table'[End Date] = EARLIER ( 'Table'[End Date] )
                    )
                ),
            "Countrows_runningtotal",
                COUNTROWS (
                    FILTER (
                        'Table',
                        'Table'[File name] = EARLIER ( 'Table'[File name] )
                            && 'Table'[Policy] <= EARLIER ( 'Table'[Policy] )
                    )
                )
        ),
        [Count_] = [Countrows_runningtotal]
    )
RETURN
    IF (
        COUNTROWS ( t ) = COUNTROWS ( ALLSELECTED ( 'Table'[Policy] ) ),
        SUMX ( t, [Amt] )
    )

Best Regards,

Icey

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

[amitchandak]

@Anonymous , Create measures like

Policies = concatenatex(Table,Table[Policy],",")

Amt Measure = Sum(Table[Amt])

Use Matrix with file name on column and measure in values

and use Show On row

https://www.burningsuit.co.uk/blog/2019/04/7-secrets-of-the-matrix-visual/

[Anonymous]

Hi Amit,

The challenging part of the problem is how to compare if start and end date are the same for all policies and sum up only those policies where start and end date is same. If the dates are different, value should not add up and not display anything.

Regards,

Amit Darak