Forum Discussion
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 enabled.But when I apply sum to discount column it gives same total in every row as well as I get total at bottom. But I want just total at end and sales order wise discount. How to achieve this?
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.
7 Replies
- Idrissshatila
Super User
Hello sudhanshu45 ,
if you could show us what's happening with you, since even if you summed the discount it should't show the same on every row.
if it shows the same for every row then there might be a missing relationship between the discount and the second table having the other column.
- Ashish_Mathur
Hi,
Looks like some problem with relationships. Share the download link of the PBI file.
- danextian
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?
Yes there is many to one relationship & both columns are from different tables.
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.
