topic Re: DAX: Calculate Total Sales based on product type in DAX Commands and Tips

DAX: Calculate Total Sales based on product type

LABrowne — Wed, 25 Oct 2023 09:30:52 GMT

Hello there,

I am currently creating a power BI report and using DAX to find total sales by product type. Confusingly 1 product has be calculated using 'Net Sales' and not 'Gross Sales'. Please see the formula I have created below:

Measure =

VAR NETSALES =

CALCULATE([NetSales])

RETURN

SWITCH(

SELECTEDVALUE(Product[ProductTypeId]),

2, NETSALES,

1, [GrossSales],

3, [GrossSales],

4, [GrossSales],

5, [GrossSales]

)

It works for individual sales on the power BI visuals but I need it to simultaneously add up as well. So if you looked at the table all the individual values are correct but the whole column is not summing up if that makes sense.

Any help would be greatly appreciated!

Thanks,

Luke

Re: DAX: Calculate Total Sales based on product type

123abc — Wed, 25 Oct 2023 09:43:39 GMT

It seems like you want to create a DAX measure in Power BI to calculate the total sales by product type, where for Product Type 2, you want to use 'Net Sales', and for other product types (1, 3, 4, 5), you want to use 'Gross Sales'. However, your current DAX measure is returning individual values, and you want it to calculate the total sales as well.

You can achieve this by modifying your DAX measure slightly. You can use the SUMX function to iterate through the table and calculate the sum of sales for each product type. Here's how you can modify your measure:

Total Sales by Product Type =
SUMX(
VALUES(Product[ProductTypeId]), -- Iterate through unique Product Types
VAR NETSALES = CALCULATE([NetSales])
RETURN
SWITCH(
Product[ProductTypeId],
2, NETSALES,
1, [GrossSales],
3, [GrossSales],
4, [GrossSales],
5, [GrossSales]
)
)

This measure will iterate through unique Product Types and calculate the sales for each type using your specified logic (Net Sales for Product Type 2, Gross Sales for others), and then sum these values to give you the total sales by product type.

Now, when you use this measure in your Power BI visuals or tables, it should correctly display the total sales by product type, considering the individual sales values for each product type and summing them up as well.

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.

Re: DAX: Calculate Total Sales based on product type

EylesIT — Wed, 25 Oct 2023 09:49:08 GMT

LABrowne, my suggested solution is to change your measure formula to this:

Measure = SUMX( VALUES(Product[ProductTypeId]), SWITCH(Product[ProductTypeId], 1, [NetSales], [GrossSales] ) )

Re: DAX: Calculate Total Sales based on product type

LABrowne — Wed, 25 Oct 2023 12:45:42 GMT

Thank you!

Re: DAX: Calculate Total Sales based on product type

LABrowne — Wed, 25 Oct 2023 15:42:03 GMT

Thanks!