Forum Discussion

bvanderwatt's avatar
bvanderwatt
Icon for Helper III rankHelper III
4 years ago

Category Percentage contribution

Good Day

 

I am trying to see the % contribution of the categories to FY22 Sales. 

 

The below formula works fine, but I want to add it to a matrix which is dependent on a date slicer. As soon as I add the below measure to the matrix (which is dependent on the date slicer,) then my measure doessn't work. How can I amend the below measure so that it's not dependent on the date slicer. "Edit interactions" is not an option for this matrix. 

 

Also, when I add range below the category, I want to see the % contribution that the range made to the category. It's now only showing 100% for the range contribution. 

 

Any help will be much appreciated. 🙂 

 

Category % contribution =
VAR vNumerator = [FY22Sales]
VAR vDenominator =
CALCULATE(
[FY22Sales],
ALLSELECTED(
ProductList[CATEGORY]
))
VAR vResult =
DIVIDE ( vNumerator, vDenominator )
RETURN
vResult
Need Help
Show and Tell

9 Replies

  • AilleryO's avatar
    AilleryO
    Icon for Memorable Member rankMemorable Member
    4 years ago

    Hi,

     

    Looking at your table you have 2 levels of grouping,

    BELTZ is 1, and
    ALLEYCAT, ALPINIST is 2.

     

    So you you'll need something like this :

    SR Total is the sum of a column with Sales Revenu values.

     

    % SR / Product in its Category =
    VAR Cat_Current = SELECTEDVALUE( Products[Category] )//Category current
    VAR SR_Cat = CALCULATE( [SR Total],
    FILTER( ALL( Products ) , Products[Category] = Cat_Current ) ) //Calculation of total for category
    //function ALL takes into account all products not only the one on the line
    RETURN
    DIVIDE(
    [SR Total],//Sales revenue calculated in the filter context (so for one line of the table)
    CA_Cat)//Sales revenue for the whole category

     

    In your case you should replace SR Total by your FY22Sales, Products will be for you the column with the value ALLEYCAT, ALPINIST... and Category the column with the value BELTZ.

     

    Is it OK with you ?

  • bvanderwatt's avatar
    bvanderwatt
    Icon for Helper III rankHelper III
    4 years ago

    Hi v-janeyg-msft  AilleryO 

     

    Thank you very much. 

     

    I have incorporated your formulas. It's working perfect but only if I add the measures in two different columns. 

     

    % of Total Sold (ALLSELECTED): This is showing the % contribution of the range to the entire financial year's sales. 

    Category/ Range % Contribution: This is showing the % contribution of the range to the category

     

    Ideally, I want to display it in one column. 

     

    POWER BI SNAPSHOT & FORMULAS:

     

    % of Total Sold (ALLSELECTED) =
    VAR SelectedQuantitySold =
    CALCULATE(
    [FY22 Nsls],
    ALLSELECTED(
    )
    )
    VAR Ratio =
    DIVIDE(
    [FY22 Nsls],
    SelectedQuantitySold,
    "0"
    )
    RETURN
    Ratio

     

    Category/ Range % Contribution =
    VAR Cat_Current = SELECTEDVALUE( ProductList[CATEGORY] )
    VAR SR_Cat =
    CALCULATE( [FY22Sales],
    FILTER( ALL( ProductList ) , ProductList[CATEGORY] = Cat_Current ) )
    RETURN
    DIVIDE(
    [FY22Sales],
    SR_Cat)

     

    • AilleryO's avatar
      AilleryO
      Icon for Memorable Member rankMemorable Member
      4 years ago

      Hi,

       

      Not sure about expected results, but if you want to concatenate the 2 results :

      NewMeasure = % of Total Sold (ALLSELECTED) & Category/ Range % Contribution

       

      should work ? And do not forget to mark post as Solved for other members. Thanks.

  • AilleryO's avatar
    AilleryO
    Icon for Memorable Member rankMemorable Member
    4 years ago

    Hi,

     

    Try to put directly in the DIVIDE your FY22Sales, otherwise it will not be calculated in the context.

    So it should be :

    Category % contribution =
    VAR vDenominator =
    CALCULATE(
    [FY22Sales],
    ALLSELECTED(
    ProductList[CATEGORY]
    ))
    RETURN
    DIVIDE ( [FY22Sales] , vDenominator )
     
    Let us know if it works
    • bvanderwatt's avatar
      bvanderwatt
      Icon for Helper III rankHelper III
      4 years ago

      Hi. I tried it but unfortunately it didn't make any difference. As soon as the date slicer is added, then the % contribution falls away. 😞 

       

       

       

       

  • AilleryO's avatar
    AilleryO
    Icon for Memorable Member rankMemorable Member
    4 years ago

    Could you give us the formula you used for FY22Sales ?

    Do you have Date Table or none ?

    Would it be possible to provide some dummy datas ?

    • bvanderwatt's avatar
      bvanderwatt
      Icon for Helper III rankHelper III
      4 years ago

      Hi. Please see below. 

       

      Yes, I have a date table 🙂 

       

      FY22Sales =
      CALCULATE(
      SUM(
      'Combined Sales'[Inv+Order]),
      FILTER(
      'Calendar',
      'Calendar'[Fin Year] = "FY22"
      )
      )
      • v-janeyg-msft's avatar
        v-janeyg-msft
        Icon for Community Support rankCommunity Support
        4 years ago

        Hi, bvanderwatt 

         

        Have your problem been solved? If yes, you can mark the helpful answer as solution to close the thread. If not, please feel free to ask me.

         

        Best Regards,
        Community Support Team _ Janey

  • AilleryO's avatar
    AilleryO
    Icon for Memorable Member rankMemorable Member
    4 years ago

    When you put inside your table FY22Sales do you have the expected amount on all lines ?

    If yes :

    CALCULATE ( FY22Sales , ALL( Category ) )

    would calculate the total for all category, and :

    DIVIDE ( FY22Sales , CALCULATE ( FY22Sales , ALL( Category ) ) )

    could work ?

    Tell us