Is there a way to do a subtotal sum?

[Anonymous]

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?

[Anonymous]

without seeing your data, something like :

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

[Anonymous]

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.

[Anonymous]

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. 

[Anonymous]

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)

[Anonymous]

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.