Forum Discussion

Chalklands's avatar
Chalklands
Icon for Helper I rankHelper I
6 years ago

Help with All, Allexcept in a calculation

Hi,

Been trying different ways of doing this but can't work out the correct method.

 

I have sales data as follows:

 

MonthCustomerProduct CategorySales
JanuaryCustomer AMeat25
JanuaryCustomer BFish75
JanuaryCustomer AFish60
FebruaryCustomer BMeat40
FebruaryCustomer AMeat30

 

I want to calculate what percentage of sales of a category a customer has in a month (Depending on which Customer, Category and Month are selected using filters.

 

So for example Customer B had 55.6% of Fish Sales in January.

 

Can anybody help please?

Need Help

9 Replies

  • az38's avatar
    az38
    Icon for Community Champion rankCommunity Champion
    6 years ago

    Hi Chalklands 

    try a measure

    Measure = divide(calculate(sum(Table1[Sales]));calculate(sum(Table1[Sales]);allexcept(Table1;Table1[Product Category];Table1[Month])))

    do not hesitate to give a kudo to useful posts and mark solutions as solution

    LinkedIn

  • parry2k's avatar
    parry2k
    Icon for Super User rankSuper User
    6 years ago

    Chalklands I have broken down in 3 measure for better readbility and understanding

     

    Sum of Sales = SUM ( Rate[Sales] ) 

Sum of Sales - All = 
CALCULATE ( 
[Sum of Sales], 
ALLEXCEPT ( Rate, Rate[Month], Rate[Product Category] ) 
)

% of Sales = DIVIDE( [Sum of Sales], [Sum of Sales - All] )

     

    It provides better performance in case you are working on large dataset and also easy to debug or make changes in the future. Cheers!!

     

    • Chalklands's avatar
      Chalklands
      Icon for Helper I rankHelper I
      6 years ago

      Thanks for the help.

      Unfortunately I can't get the 'Sum of Sales - all' measure to work.

      Not sure if its because I have Marketing category in a table Called 'Product list' (Linked to the main data table by product code, 1 to many) and the Month in a 'Date' table (Linked to the main data table by invoice date)? Do you have to add an Allexcept expression for both separately?

       

      Thanks in advance for any help!

      • parry2k's avatar
        parry2k
        Icon for Super User rankSuper User
        6 years ago
        Can you share the full data model. And relationship diagram
  • v-lionel-msft's avatar
    v-lionel-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi Chalklands ,

    Please try this measure:

    % of sales = 
VAR x=
CALCULATE(
    SUM(Sheet1[Sales]),
    ALLEXCEPT(
        Sheet1,
        Sheet1[Product Category],
        Sheet1[Month]
    )
)
VAR y = 
CALCULATE(
    MAX(Sheet1[Sales]),
    ALLEXCEPT(
        Sheet1,
        Sheet1[Product Category],
        Sheet1[Month],
        Sheet1[Customer]
    )
)
RETURN
DIVIDE(y, x)

     

    Best regards,
    Lionel Chen

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • Chalklands's avatar
      Chalklands
      Icon for Helper I rankHelper I
      6 years ago

      Thanks Lionel.

       

      This works for an individual month, but if you include both months the result isn't calculating correctly.

       

      For example if both months are selected the result should be:

      CategoryCustomerSales%
      MeatCustomer A5558%
      MeatCustomer B4042%
       Total95 

       

      The result using the DAX expression in Power BI is:

      Meat     Customer A   55      32%

      Meat     Customer B   40       42%

       

      So, for some reason it is incorrect for Customer A as Customer A has sales in both months.

       

      I'll keep trying!

       

      Thanks again.

       

      Pete

       

       

       

      • v-lionel-msft's avatar
        v-lionel-msft
        Icon for Community Support rankCommunity Support
        6 years ago

        Hi Chalklands ,

        I don't meet the error.

        As you see, when I filter the month, the result is you want.

        The reason is my data model looks like this, all the columns are from the same table:

         

        Do your columns come from different tables?

        If yes, please give me a sample data model, the main thing is to tell me the relationship between different tables.


        Best Regards,
        Lionel Chen