Forum Discussion

Almiwork's avatar
Almiwork
Frequent Visitor
4 years ago

Measure misbehaving when adding more columns to matrix

Hi all,

I'm in need of a measure that allows me to distribute sales coming from sub_channelgrouping = Unknown into my other rows. This should be based on the share that each row have of the total sales without unknowns. I've got my measure to work when the only category column inside my table is sub_channelgrouping. But i would like to be able to add more columns to the matrix without disrupting the measure.
First picture: working totally as intended

 

 


Second picture: no longer splits out the "unknown" into the other rows

 

 



Measure used for the sales adjusted: 

IF(SELECTEDVALUE('Table'[Sub_ChannelGrouping]) in {"Unknown"} || isblank(SELECTEDVALUE('Table'[Sub_ChannelGrouping])) , BLANK(),
SUM('Table'[sales]) + (CALCULATE(SUM('Table'[sales]), 'Table'[Sub_ChannelGrouping] = "Unknown") * SUM('Table'[sales])/CALCULATE(SUM('Table'[sales]), 'Table'[Sub_ChannelGrouping] <> "Unknown")))
 
Update: Fixed by adding a "Removefilters" to the main_channelgrouping. Also note that if you are sorting the sub or main channelgrouping, you would have to remove that filter aswell!
 
Need Help

6 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    Almiwork , You need to consider, You need to divide first or Multiply, that can make a lot of diff. as of now it is multiplication first.

     

    IF(SELECTEDVALUE('Customer Status & Traffic Sales'[Sub_ChannelGrouping]) in {"Unknown"} || isblank(SELECTEDVALUE('Customer Status & Traffic Sales'[Sub_ChannelGrouping])) , BLANK(),
    SUM('Customer Status & Traffic Sales'[actual_sales]) + (CALCULATE(SUM('Customer Status & Traffic Sales'[actual_sales]), 'Customer Status & Traffic Sales'[Sub_ChannelGrouping] = "Unknown") * SUM('Customer Status & Traffic Sales'[actual_sales])/CALCULATE(SUM('Customer Status & Traffic Sales'[actual_sales]), 'Customer Status & Traffic Sales'[Sub_ChannelGrouping] <> "Unknown")))

    • Almiwork's avatar
      Almiwork
      Frequent Visitor
      4 years ago

      Not sure what you mean by divide or multiply first? 
      Your listed code produced exactly the same outcome as mine, doesn't work when adding the second column to the table.

      • amitchandak's avatar
        amitchandak
        Super User
        4 years ago

        Almiwork , I have done only chance in if. But this part you need to revisit

         

        (CALCULATE(SUM('Customer Status & Traffic Sales'[actual_sales]), 'Customer Status & Traffic Sales'[Sub_ChannelGrouping] = "Unknown") * SUM('Customer Status & Traffic Sales'[actual_sales]) / CALCULATE(SUM('Customer Status & Traffic Sales'[actual_sales])

         


        Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.