Sales total combine.

[dkbbr]

How you guy's find a combined total sales? I find Total sales for each of them but it's customers and drivers separated. how you combine them. adding new column like "Role" using dax( i don't really need extra column)

Date	CustomerPkId	CustomerName	DriverPkid	DriverName	SaleAmount
24/01/01	1001	Jhon	2001	Brain	1000
24/01/02	1002	jon	2002	Brown	1250
24/01/02	1003	jin	2001	Brain	4502
24/01/03	1002	jon	2003	Tom	12515
24/01/04	1001	jhon	2004	Toms	1412

I wanna look like this after writing DAX :

Name	Role	Sale
Jhon	Customer	2412
Brain	Driver	5502

[v-jiewu-msft]

Hi @dkbbr ,

Based on the description, @rajendraongole1 the response provides the desired results. It should be helpful to you.

If you have solved the problem, please please consider Accept it as the solution to help the other members find it more quickly.

Best Regards,

Wisdom Wu

[rajendraongole1]

Hi @dkbbr - you can create a new calculated table with combining the sales data for customers and drivers.

CombinedSales =

UNION(

    SELECTCOLUMNS(

        FILTER('Cmbine', NOT(ISBLANK('Cmbine'[CustomerPkId]))),

        "Name", 'Cmbine'[CustomerName],

        "Role", "Customer",

        "Sale", 'Cmbine'[SaleAmount]

    ),

    SELECTCOLUMNS(

        FILTER('Cmbine', NOT(ISBLANK('Cmbine'[DriverPkid]))),

        "Name", 'Cmbine'[DriverName],

        "Role", "Driver",

        "Sale", 'Cmbine'[SaleAmount]

    )

)

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!

[dkbbr]

"The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value"?