How to sum values basing on a slicer (column1) and a reference or group ID (another column2)?

[iamriz]

Hi everyone!

I have a sample Power BI test data  / scenario in the following link: https://drive.google.com/file/d/1EGEfkwvkozr7FnuTfGWR9tcZKSPk5hT5/view?usp=sharing

What I want to achieve is upon selecting the productId on the slicer on the left (in the above image), it will get the shared sales where when ReferenceGroupId is not blank, it will get/use the sales from the referenced product. For instance, if I select Products 2 and 3, I want that the sum of sales in TABLE2 would show as below:

ProductId  |  Sum of Sales

2                |  150,000

3                |  150,000

And when I select all products in the slicer (2~6), I want to get the total value of 310,000 with sales of products 2 to 3 only getting a sum of 150,000 even without selecting product 1 (since it is a shared sales value) and be able to show it in a Card Visual.

The problem seems to be a bit simple, but I have already spent few hours trying to address it. Any direction or clue on how to solve this will be much appreciated. Thank you!

[v-yangliu-msft · Accepted solution]

Hi  @iamriz ,

Here are the steps you can follow：

1. Create measure.

Measure =
var _select=SELECTEDVALUE('SalesInfo'[ProductId])
var _sum=CALCULATE(SUM('SalesInfo'[Sales]),FILTER(ALL('SalesInfo'),'SalesInfo'[ProductId]<_select))
var _rp=CALCULATE(SUM('SalesInfo'[ReferenceProductId]),FILTER(ALL('SalesInfo'),'SalesInfo'[ProductId] = _select))
return
IF(
    _rp <> BLANK(), _sum,CALCULATE(SUM('SalesInfo'[Sales]),FILTER(ALL('SalesInfo'),'SalesInfo'[ProductId] = _select))
)

Sum of Sales =
VAR __table = SUMMARIZE('SalesInfo',[ProductId],"__value",[Measure])
var _productid=SELECTCOLUMNS('SalesInfo',"1",[ProductId])
RETURN
IF(HASONEVALUE('SalesInfo'[ProductId]),[Measure],
SWITCH(
TRUE(),
1 in _productid||2 in _productid||3 in _productid,
SUMX(FILTER(ALL('SalesInfo'),'SalesInfo'[ProductId]=1),'SalesInfo'[Sales])+SUMX(FILTER(__table,NOT([ProductId] in {1,2,3})),[__value]),
SUMX(__table,[__value])))

2. Result:

Choose 3, 4, 5, 6, and the result is:

Choose 1, 2, 3, 4, 5, 6, and the result is:

Choose 1, 2, 3, the result is:

Does this match your expected result.

Best Regards,

Liu Yang

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

[v-yangliu-msft]

Hi  @iamriz ,

Here are the steps you can follow：

1. Create measure.

Measure =
var _select=SELECTEDVALUE('SalesInfo'[ProductId])
var _sum=CALCULATE(SUM('SalesInfo'[Sales]),FILTER(ALL('SalesInfo'),'SalesInfo'[ProductId]<_select))
var _rp=CALCULATE(SUM('SalesInfo'[ReferenceProductId]),FILTER(ALL('SalesInfo'),'SalesInfo'[ProductId] = _select))
return
IF(
    _rp <> BLANK(), _sum,CALCULATE(SUM('SalesInfo'[Sales]),FILTER(ALL('SalesInfo'),'SalesInfo'[ProductId] = _select))
)

Sum of Sales =
VAR __table = SUMMARIZE('SalesInfo',[ProductId],"__value",[Measure])
RETURN
IF(HASONEVALUE('SalesInfo'[ProductId]),[Measure],SUMX(__table,[__value]))

2. Result:

Select 4, 5, 6, and 160,000 will be displayed:

Choose 3, 4, 5, 6 products, then 310,000 will be displayed:

Best Regards,

Liu Yang

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

[iamriz]

Hi @v-yangliu-msft , thanks for your help. But, choosing 3, 4, 5, 6 products should display 310,000 on the card visual. In the same way that choosing 1,2,3,4,5,6 products should also display 310,000. Since products 1,2,3 has a combined sales of 150,000. Selecting one or more among 1,2,3 products on the slicer should only have a max sales of 150,000.

[v-yangliu-msft]

Hi  @iamriz ,

Here are the steps you can follow：

1. Create measure.

Measure =
var _select=SELECTEDVALUE('SalesInfo'[ProductId])
var _sum=CALCULATE(SUM('SalesInfo'[Sales]),FILTER(ALL('SalesInfo'),'SalesInfo'[ProductId]<_select))
var _rp=CALCULATE(SUM('SalesInfo'[ReferenceProductId]),FILTER(ALL('SalesInfo'),'SalesInfo'[ProductId]=_select))
return
IF(
    _rp <> BLANK(), _sum,SUMX(ALL('SalesInfo'),'SalesInfo'[Sales])
)

2. Result:

When the slicer is 2, 3, the result is:

When the slicer is another value, the result is:

Best Regards,

Liu Yang

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

[iamriz]

Hi @v-yangliu-msft , thanks for your response. Greatly appreciate it. There is a problem left with your suggested solution. Even if I only select, 4,5,6 products on the slicer, the total is still 310,000 if I show "Measure" on a Card Visual. I wanted that 160,000 will be showed if only 4,5,6 is selected. Then, if 3~6 products are selected, then 310,000 will show. Kindly advise, thank you!