Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

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

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

 
Need Help

4 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      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",

       

      TypeAmount
      Allied Sales30,000
      Outside Sales40,000
      Total Sales 

       

      Hope this helps 

      • rmbuchan's avatar
        rmbuchan
        Frequent Visitor
        3 years ago

        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.  

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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