Sum with condition

[ClaireLin]

Table 1                                                         Table 2
=====                                      ========================
Name                                          Name           KPI-1        KPI-2       KPI-3
-------                                        ------------------------------------------
A-1                                             A-1                  1               2            3
A-2                                             A-2                  1               2            3       
A-3                                             A-3                  1               2            3
B-1                                             B-1                  1               2             3 
B-2                                             B-2                  1               2             3
B-3                                             B-3                  1               2             3

                                                  A-1                  1               2            3

                                                  A-2                  1               2            3 

                                                  A-3                  1               2            3

Hi,

I have 2 tables which are connected by "Name"

I need to sum KPIs with condition. It should be blank when the name = "A-1" or the name begin from "B"

If I make a BI report, it should look like this:
         

                   KPI-1           KPI-2           KPI-3

-------------------------------------------------------

A-1
A-2            2                   4                    6
A-3            2                  4                     6
B-1
B-2
B-3
---------------------------------------------------------
Total         4                     8                    12

I tried adding a column as below:

KPI-1 =

VAR name = RELATED('Table 1'[name])

VAR KPI1 = CALCULATE(SUM('Table2'[KPI-1])

RETURN

IF (

LEFT( name, 1) = "B" || RELATED(name) = "A-1",

BLANK (),

KPI1

)

Then created a measure to sum it.

It works for one KPI. But if I copy the same formula to KPI-2, then it doesn't work anymore.
Could any one sugguest me what I should do? It can be an added column or measure directly.

Thank you!

[HotChilli]

If you put a simple SUM as the aggregation for each KPI field, create a table visual with Name and each kpi field, then using the Filter pane (Advanced filtering), add the conditions you want to filter the name field (you can add multiple conditions with 'and').  You should be able to display the 2 rows with figures (and the totals will be correct)

So the output won't be exactly what you specified but powerbi doesn't like displaying rows with blanks.

Hope it helps.

[HotChilli]

Please post the data as text and then show what the desired output is.

Measures are evaluated according to context so we need to see the output format.

[ClaireLin]

Hi, @HotChilli 

Thank you for the suggestion. I made a change in the text. Would you please advise if it's sufficient? Or I should put some more info there? Thank you for your help!