Forum Discussion

Gekko1's avatar
Gekko1
Helper I
3 years ago
Solved

Incorrect Totals

My problem is as it follows: I have 2 tables, the first one has the amount of products per date and the company ID from where that product is; the second one has the company ID and the company name. In order to calculate the amount of products  that each company have in the last date of the selected month I wrote the Dax code as shown below:

AMOUNT = CALCULATE(SUM(ProductTable[AvailableAmount]),FILTER(ProductTable,ProductTable[EventDate]=MAX(ProductTable[EventDate])))

The tables have the format shown below:


When I put the Company Name as a row header in a matrix visual I am getting the wrong total. I've already tried the SUMMARIZE+SUMX, the IF+HASONFILTER and the SUMX methods but none of these actually worked. The DAX code of my attempts are listed below:

ATTEMPT1= SUMX(CompanyTable,CALCULATE(SUM(ProductTable[AvailableAmount]),FILTER(ProductTable,ProductTable[EventDate]=MAX(ProductTable[EventDate]))))

ATTEMPT2= 
VAR X = CALCULATE(SUM(ProductTable[AvailableAmount]),FILTER(ProductTable,ProductTable[EventDate]=MAX(ProductTable[EventDate]))))
RETURN
SUMX(SUMMARIZE(CompanyTable,CompanyTable[CompanyName]),X)

ATTEMPT3= 
VAR X =CALCULATE(SUM(ProductTable[AvailableAmount]),FILTER(ProductTable,ProductTable[EventDate]=MAX(ProductTable[EventDate]))))
RETURN
IF(HASONEFILTER(CompanyTable[CompanyName]),X,SUMX(CompanyTable,X))
 
  • v-yadongf-msft's avatar
    v-yadongf-msft
    3 years ago

    Hi Gekko1 ,

     

    If your goal is to calculate the amount of products  that each company have in the last date of the selected month, please try following steps:

     

    This is my test table:

     

     

    Create a new column:

    Month = FORMAT([Date],"mmmm")

     

    Try following measure:

    Measure = CALCULATE(
    SUM('Table'[Amount]),
        FILTER('Table','Table'[Month] = SELECTEDVALUE('Table'[Month]) && 
                        'Table'[Company ID] = SELECTEDVALUE('Table'[Company ID]) && 
                        'Table'[Date] = MAX('Table'[Date]))
                     )

     

    Is this the result you want?

     

    Best regards,

    Yadong Fang

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

1 Reply

  • v-yadongf-msft's avatar
    v-yadongf-msft
    Community Support
    3 years ago

    Hi Gekko1 ,

     

    If your goal is to calculate the amount of products  that each company have in the last date of the selected month, please try following steps:

     

    This is my test table:

     

     

    Create a new column:

    Month = FORMAT([Date],"mmmm")

     

    Try following measure:

    Measure = CALCULATE(
    SUM('Table'[Amount]),
        FILTER('Table','Table'[Month] = SELECTEDVALUE('Table'[Month]) && 
                        'Table'[Company ID] = SELECTEDVALUE('Table'[Company ID]) && 
                        'Table'[Date] = MAX('Table'[Date]))
                     )

     

    Is this the result you want?

     

    Best regards,

    Yadong Fang

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