DAX: Calculate Total Sales based on product type

[LABrowne]

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

[123abc · Accepted solution]

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.

[EylesIT · Accepted solution]

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

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

[LABrowne]

Thank you!

[123abc · Accepted solution]

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.

[LABrowne]

Thanks!