Forum Discussion

pani_victoria's avatar
pani_victoria
Icon for Helper III rankHelper III
1 year ago

sum sales by a specific condition

Colleagues, hello!

I have dataset

How can I calculate add sales site?

These are sales with an empty site field, but with the same document number, where the site is not empty

 

Please, help me )

Need Help
Show and Tell

4 Replies

  • rohit1991's avatar
    rohit1991
    Icon for Super User rankSuper User
    1 year ago

    Hi pani_victoria 
    To calculate the "add sales site" column in Power BI, you need to sum sales where the site field is empty but match document numbers where the site is not empty.

    Solution in DAX:

    You can create a calculated column using this DAX formula:

    Add Sales Site = 
VAR SalesWithSite = 
    CALCULATE(
        SUM(YourTable[sales]),
        YourTable[site] <> BLANK()
    )
VAR SalesWithoutSite = 
    CALCULATE(
        SUM(YourTable[sales]),
        YourTable[site] = BLANK()
    )
RETURN 
    IF(YourTable[site] = BLANK(), SalesWithSite, BLANK())

    Explanation:

    1. SalesWithSite: Calculates the total sales where the site is NOT blank.
    2. SalesWithoutSite: Calculates sales where the site is blank but has the same document number where a site exists.
    3. Final Return: Assigns the matching sales value to the row where site is blank.

     

    • pani_victoria's avatar
      pani_victoria
      Icon for Helper III rankHelper III
      1 year ago

      Hi

       

       

      VAR SalesWithoutSite = 
    CALCULATE(
        SUM(YourTable[sales]),
        YourTable[site] = BLANK()

       

      2. SalesWithoutSite: Calculates sales where the site is blank but has the same document number where a site exists.

       

       

      The id doc  is not taken anywhere in the formula. Is this true?

    • pani_victoria's avatar
      pani_victoria
      Icon for Helper III rankHelper III
      1 year ago

      Hi

      I have Internet Sales = this is the sum of the sales where the site <> is empty
      And there are additional sales = this is the sum the sales that have the same document number as the site sales, but their site = empty. That's the sum I need to calculate.