Use Switch true to replace a blank value in a matrix table by a calculation

[Anonymous]

Hi all,

I am completely new to Power BI, I have been trying to use a Switch True to replace blank value into a Matrix table, but I can't get it to work if I use a calculation of other fields, if I type the amounts on the code it works fine,

But I am trying to get it to calculate a margin= Sales-COGS and input this into a row.

Measure = var CurrItem=SELECTEDVALUE(Sheet1[Type])

Return

SWITCH(TRUE(),

CurrItem="Total Sales",[Total Sales],

CurrItem="Total COGS",[Total COGS],

CALCULATE(sum(Sheet1[Amount]),filter(Sheet1, Sheet1[O&P]=CurrItem)))

Any help will be really appreciated,

Thanks

Alban

[v-shex-msft]

Hi @Anonymous,

According to your description, I think you need to check your records to confirm if these blank parts really exist in your table. 

If all records exist in your table, you can simply replace them based on measure formula and if statement.

If some of the records didn't exist in your table and they only work as the placeholder that generated by matrix column, row categories analysis layout. I think You need to extract the category fields which not have all matched records to new tables, then you can use two table fields as matrix column/row fields.

After these steps, you can write a measure formula to lookup value based on the current category data label and set a default result with not matched records and use on the matrix value field.

Regards,

Xiaoxin Sheng

[amitchandak]

@Anonymous 

Are you trying to build a measure slicer ?

https://community.powerbi.com/t5/Desktop/Slicer-MTD-QTD-YTD-to-filter-dates-using-the-slicer/td-p/500115

[Anonymous]

Thanks for your reply @amitchandak  ,

No it won't be a slicer, if you look at the matrix below, I am trying to replace the value for "Total Sales" (which is blank for now) using switch true, by calculating a sub total of all sales, ie adding "Allied sales" and "Outside sales",

Type	Amount
Allied Sales	30,000
Outside Sales	40,000
Total Sales

Hope this helps 

[rmbuchan]

Did you ever figure this out? I'm having the exact same issue. If I replace the calculated item [Total Sales] with a hard number, the switch function shows the number, but the calculated item is always null.