topic Re: Is there a way to do a subtotal sum? in DAX Commands and Tips

Is there a way to do a subtotal sum?

Anonymous — Mon, 10 Jun 2019 18:36:49 GMT

I'm trying to weight a MAPE formula by revenue, but I can't figure out how to just get the subtotal sum of the quantity*revenue (I only want to sum the visible cells which are orders from January to April).

I first created a column that was just Quantity * Revenue:

Quantity*Revenue = [Order Quantity] * [Revenue]

I then created a calculated column for the weighted price as follows:

Weighted Price= [Quantity*Revenue] / SUMX('Order Data', [Quantity*Revenue])

I then multiplied this by the MAPE as a measure (I already had MAPE as a measure):

WMAPE: [MAPE] * [Sum of Weighted Price].

The problem is that the SUMX of Weighted Price takes the sum of the entire orders list, not just the visible rows, so the number is extremely small. Is there anyway to get a subtotal sum like you can do in excel?

Re: Is there a way to do a subtotal sum?

Anonymous — Mon, 10 Jun 2019 18:07:30 GMT

without seeing your data, something like :

SUMX( 
    VALUES ( <whatever is on rows>),
   [measure]
)

Re: Is there a way to do a subtotal sum?

Anonymous — Mon, 10 Jun 2019 18:35:27 GMT

Here's the data. I want to weight the MAPE by the weighted price column.

Something like: WMAPE:= [MAPE] * [Sum of Weighted Price]

The problem is the [Sum of Weighted Price] includes all the orders, not just the filtered rows. For that column I used:

=[Quantity*Revenue]/SUMX('Orders Data',[Quantity*Revenue])

Hope this makes it more clear! Thanks.

Re: Is there a way to do a subtotal sum?

Anonymous — Mon, 10 Jun 2019 18:41:24 GMT

You're going to want these to be measures and not calculated columns. If you upload some data I can help you out if you want. Onedrive works best.

Re: Is there a way to do a subtotal sum?

Anonymous — Mon, 10 Jun 2019 19:00:04 GMT

Not sure how to upload files on here but I created a measure Q*R:

Q*R:=SUMX('Orders Data',[Order Quantity])*SUMX('Orders Data',[Revenue])

How would I make a measure instead of the weighted price column? I tried:

Weighted Price :=[Q*R]/SUMX('Orders Data',[Q*R])

but that gave me a total of 906.76 (I want the total to be 1)

Re: Is there a way to do a subtotal sum?

Anonymous — Mon, 10 Jun 2019 20:27:16 GMT

you can upload an excel file or power bi file using Onedrive or dropbox and then posting the link here. Would be much much easier to see what you are working with here.