Forum Discussion

sudhanshu45's avatar
sudhanshu45
Frequent Visitor
9 months ago
Solved

Total not showing in table visual

i have sales order number column and discount column. When I create table with both this column , discount column is not summarised I get sales order wise discount. here at bottom total is not enab...
Need Help
  • v-sshirivolu's avatar
    v-sshirivolu
    9 months ago

    Hi sudhanshu45 ,
    Thanks for sharing the details. The issue is happening because your two columns come from different tables with a many-to-one relationship. In this scenario, a raw column from the one side (Discount) cannot automatically compute row level values and totals correctly in the table visual.

    The recommended approach is to create a DAX measure that handles both row level and total calculations. Here’s a working example I have tried :

    DiscountPerOrder =
    IF(
    HASONEVALUE(SalesOrderTable[SalesOrderNumber]),
    LOOKUPVALUE(
    DiscountTable[DiscountAmount],
    DiscountTable[SalesOrderNumber],
    VALUES(SalesOrderTable[SalesOrderNumber])
    ),
    SUMX(
    SalesOrderTable,
    LOOKUPVALUE(
    DiscountTable[DiscountAmount],
    DiscountTable[SalesOrderNumber],
    SalesOrderTable[SalesOrderNumber]
    )
    )
    )
    This approach ensures proper row-level values and accurate totals even when the columns come from different tables in a many-to-one relationship.


    Please find the attached .pbix file for your reference.

danextian's avatar
danextian
Super User
9 months ago

Hi sudhanshu45 

Are the sales order and discount columns from the same table? If not, as others already mentioned, is there a relationship from sales order to the table  where the discount is in?

sudhanshu45's avatar
sudhanshu45
Frequent Visitor
9 months ago

Yes there is many to one relationship & both columns are from different tables. 

  • v-sshirivolu's avatar
    v-sshirivolu
    Community Support
    9 months ago

    Hi sudhanshu45 ,
    Thanks for sharing the details. The issue is happening because your two columns come from different tables with a many-to-one relationship. In this scenario, a raw column from the one side (Discount) cannot automatically compute row level values and totals correctly in the table visual.

    The recommended approach is to create a DAX measure that handles both row level and total calculations. Here’s a working example I have tried :

    DiscountPerOrder =
    IF(
    HASONEVALUE(SalesOrderTable[SalesOrderNumber]),
    LOOKUPVALUE(
    DiscountTable[DiscountAmount],
    DiscountTable[SalesOrderNumber],
    VALUES(SalesOrderTable[SalesOrderNumber])
    ),
    SUMX(
    SalesOrderTable,
    LOOKUPVALUE(
    DiscountTable[DiscountAmount],
    DiscountTable[SalesOrderNumber],
    SalesOrderTable[SalesOrderNumber]
    )
    )
    )
    This approach ensures proper row-level values and accurate totals even when the columns come from different tables in a many-to-one relationship.


    Please find the attached .pbix file for your reference.

    • v-sshirivolu's avatar
      v-sshirivolu
      Community Support
      9 months ago

      Hi sudhanshu45 ,

      I hope the above details help you fix the issue. If you still have any questions or need more help, feel free to reach out. We’re always here to support you.

       

      • v-sshirivolu's avatar
        v-sshirivolu
        Community Support
        9 months ago

        Hi sudhanshu45 ,
        I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions.