Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Matrix column grandtotal incorrect

Hi 

My matrix column grand total is coming incoorect.

Attached is the pbix file with sample data. Can anybody suggest any solution for it.

dax used is : 

datebucketsvalue = VAR _min = SELECTEDVALUE ( 'Sheet1 (2)'[min])
VAR _max = SELECTEDVALUE ( 'Sheet1 (2)'[max] ) var selectdatevalue= IF(ISFILTERED(Sheet1[sales date]),SELECTEDVALUE(Sheet1[sales date]),NOW())
RETURN
calculate(sum(Sheet1[amount]),
FILTER (
ALLEXCEPT(Sheet1,Sheet1[Busniess Area],Sheet1[BP]) ,
datediff(Sheet1[sales date],selectdatevalue,day)>= _min &&
datediff(Sheet1[sales date],selectdatevalue,day) <= _max
)
)
this is my table created for dateintervals

 

 

Regards

Shilpi

 

  • v-jingzhang's avatar
    v-jingzhang
    4 years ago

    Hi Anonymous 

     

    You can create an individual date table from the Sales table or create a date table directly. Keep this date table disconnected with the Sales table. 

     

    Then use this date table's date column in the slicer (or a table visual for filter) and in the databucketsvalue measure. The column total of the matrix will be correct then. 

     

    The row total is still incorrect, do you need to correct it?

     

    Best Regards,
    Community Support Team _ Jing
    If this post helps, please Accept it as Solution to help other members find it.

3 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    Anonymous , Change return like

     

    sumx(addcolumns( summarize(Sheet1,Sheet1[Busniess Area],Sheet1[BP] ), "_1" ,
    calculate(sum(Sheet1[amount]),
    FILTER (
    ALLEXCEPT(Sheet1,Sheet1[Busniess Area],Sheet1[BP]) ,
    datediff(Sheet1[sales date],selectdatevalue,day)>= _min &&
    datediff(Sheet1[sales date],selectdatevalue,day) <= _max
    )
    )),[_1])

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      This dax is giving wrong calculations

  • v-jingzhang's avatar
    v-jingzhang
    Community Support
    4 years ago

    Hi Anonymous 

     

    You can create an individual date table from the Sales table or create a date table directly. Keep this date table disconnected with the Sales table. 

     

    Then use this date table's date column in the slicer (or a table visual for filter) and in the databucketsvalue measure. The column total of the matrix will be correct then. 

     

    The row total is still incorrect, do you need to correct it?

     

    Best Regards,
    Community Support Team _ Jing
    If this post helps, please Accept it as Solution to help other members find it.