Power BI showing totals on subitems when there is no relationship

[Jay]

I have tables
repot which contains L1,L2,L3 colums
budget which contains L3, values columns
actual which contains L3, product, customer, values and many other columns

When creating visuals, i want to show product from actual, but then this happens

position actual budget
retail 10 10
product1 2 10
product2 8 10

How do I avoid products having values for L3, they should be blank as there is no budget for them, i.e.

position actual budget
retail 10 10
product1 2
product2 8

I know this is an issue with relationships, as they exist only on L3, but it still doesnt make sense.

[administrator@dailyexcel.net]

You can try using a measure such as

BudgetValue =
IF(ISINSCOPE('actual'[product]),BLANK(),SUM'budget'[values]))

This will check if a product is shown on your report and show blanks if that is the case.

Or

BudgetValue =
IF(ISINSCOPE('report'[L1]||'report'[L2]'report'[L3]),SUM'budget'[values]),BLANK())

This will check if your reporting levels are visible and show budget figures, and show blanks otherwise

Br,

[Author]

Posts