Forum Discussion

mh2587's avatar
mh2587
Icon for Super User rankSuper User
4 years ago
Solved

Measure total is not correct

In the I just distinctcount one of the column but in matrix the total is incorrect Single Table Sales_transaction  columns :  Customer Key , Document type , Net Amount  Dax : Customer Deliv...
Need Help
Show and Tell
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi mh2587 ,

     

    Please try calculating the value of the total column separately.

    SUMX(ALLSELECTED(table),[Customer Deliveries])

    Use ISINSCOPE() function to determine whether it is a total column and return different value.

    measure = IF(ISINSCOPE([Column filed]),[Customer Deliveries],SUMX(ALLSELECTED(table),[Customer Deliveries]))

    If I misunderstood your meaning, please share some sample data and expected result.

     

    Best Regards,

    Jay

amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
4 years ago

mh2587 , Distinctcount can be different in grand total. As that is not sum of rows

 

Other wise force it using the column in visual

 

sumx(addcolumns(summarize(Table, Table[Column]), "_1", distinctcount(Table[Col2])), [_1])

mh2587's avatar
mh2587
Icon for Super User rankSuper User
4 years ago
Customer Deliveries 2.1 = var table_ = SUMMARIZE(SALES_DOCUMENTS,
SALES_DOCUMENTS[CUSTOMER_KEY],

"Deliveries",CALCULATE(DISTINCTCOUNT(SALES_DOCUMENTS[CUSTOMER_KEY]),SALES_DOCUMENTS[DOCUMENT_TYPE] IN {"SI","SO"},SALES_DOCUMENTS[NET_AMOUNT] > 1)) return SUMX(table_,[Deliveries])
I tried this approach as well but can't get the desire output
  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    4 years ago

    mh2587 Is your table visual also summarized by CUSTOMER_KEY only? As in, you only have CUSTOMER_KEY column and Deliver 2 measure in your table? I would do it this way:

    Customer Deliveries 2.1 = 
  var table_ = SUMMARIZE(SALES_DOCUMENTS, SALES_DOCUMENTS[CUSTOMER_KEY],
    "Deliveries", [Delivery 2])
RETURN
  IF(HASONEVALUE(SALES_DOCUMENTS[CUSTOMER_KEY]),[Delivery 2],SUMX(table_,[Deliveries]))

    So use your existing measure that you know works for individual lines.

    • mh2587's avatar
      mh2587
      Icon for Super User rankSuper User
      4 years ago

      Still I get the same result not working