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 sa...
Need Help
amitchandak's avatar
amitchandak
Icon for Super User rankSuper 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
      Icon for Super User rankSuper 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.

      • Almiwork's avatar
        Almiwork
        Frequent Visitor
        4 years ago

        The order of multiplication and division doesn't matter fyi?
        I'll see if I can work together a sample dataset