Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Sales Mix % column showing -Infinity while inverse Column Shows %'s

Can someone please help me figure out why my % mix of negative categories is displaying "-Infinty" instead of a % while the % mix of my positive categories is displaying correctly?  

 

I have also noticed that if I filter to a period where there are more negatives than positives, the issue flip flops?  I can provide a screen shot and more info if needed.

 

Thank you for your help.

3 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable

      v-chuncz-msft Thank you for responding!  Here is a screen shot of the issue and then i will provide some context:

       

       

      I have created Sales "Bleeders" and Sales "Leaders".  Bleeders represents the departments that are down based off of the YoY sales measure and leaders are based off of the departments that are up YoY in sales.  I created these columns by using the following formulas

       

      Bleeders = IF([YOY Sales]<0, [YOY Sales])

      Leaders = IF([YOY Sales]>0,[YOY Sales])

       

      These formulas than put the negative YOY Sales in the bleeders column and the Positive YOY sales in the Leaders Column.  

       

      I then Created Bleeders % Mix and Total % Mix and the same for Leaders.  The difference between the % Mix column and the Total % Mix column is one formula has "ALL" and the other has "ALLSELECTED" so If i drill down i can still see the Mix % for the specific area i drilled down on and the other remains the total % mix.

       

      Bleeders % Mix = [Sales Bleeders] / CALCULATE([Sales Bleeders],ALLSELECTED(MerchandiseProduct))

      Bleeders Total % Mix = [Sales Bleeders] / CALCULATE([Sales Bleeders],ALL(MerchandiseProduct))

      Leaders % Mix = [Sales Leaders]/CALCULATE([Sales Leaders],ALLSELECTED(MerchandiseProduct))

      Leaders Total % Mix = [Sales Leaders]/CALCULATE([Sales Leaders],ALL(MerchandiseProduct))

       

      So when I was filtered on a different time frame and there were mor positive YOY Numbers, i was getting the totals to show up under the Leaders column and the "-infinty" was popping up under Bleeders.  Now that i have filtered on a timeframe that has more negative YOY numbers, the totals are under the Bleeders column and the "Infinity" numbers are under the Leaders Column.

       

      Sorry i know this is long winded but i cant seem to figure this out.  Im able to formulate % Mix no problem when its being calculated from one column but once i try to seperate it out by bleeders and leaders, this is where it all comes crashing down.

       

      Thanks and please let me know if you need more info

       

      • Anonymous's avatar
        Anonymous
        Not applicable

        Here is a screen shot of a time period with more Positive YOY Sales Numbers and as you can see, "Infinty" and the blank totals has switched from Leaders to bleeders.