Slicer to slice particular columns only in PowerBI

Question

I'm trying to create some dynamic charts in power BI.

My data looks like this:

year    Product  exp    claim_type  clCost     clCount
2010    A    0.21               0             0
2010    A    0.85               0             0
2010    A    0.15   BI          5000          1
2010    B    1.00   BI          1000          1
2010    B    0.33               0             0
2011    B    0.96               0             0
2011    B    0.16   BI          200           1
2011    B    1.00   MD          2500          1
2011    A    1.00               0             0
2011    A    0.91   MD          6000          1

End goal is have a chart with the x axis being "Year", and Y axis being the sum(clCount)/sum(exp) and two lines for product A,B. But in powerBI I want to be able to filter by claim type. So I want to only select certain rows for clCount to sum up, but sum up all the rows for exp. (Per group)

So for example, with a slicer, someone can click "BI", and the resulting value for the 2010 year andproduct A would be sum(clCount) = 1 and sum(exp) = .21+.85+.15

And for product B, similarly for the 2010 year sum(clCount) = 1 and sum(exp) = 1+.33

And ofcourse the same for the 2011 year...

Now the issue is, if I put a Slicer for the variable claim_type, and choose BI, it sums up the clCount per year correctly, but the exp, it only sums it up for the rows that contain "BI" (ie for 2010 and product the exp will only be .15 instead of .21+.85+.15. I need the sum(exp) to be of the year and product as the denominator.

Any ideas on how this can be done?

Aldert Aldert · Accepted Answer · 2019-05-26T16:04:58

Seems to me Power Bi is correct, you ask it to filter on BI, so you get only the row of 0.15. for the year 2010 product A.

You think it sums up correctly on clCount but this is not the case, you get one row which has the value 1. When you change the zero values of clCount, you will see it still gives you 1 as result.

If you want your slicer to work corrcect, you need to fill in your type in all rows.

Slicer to slice particular columns only in PowerBI

2 Answers