Friday, March 9, 2012

How to get overall total and total of specific instance?

I'm pretty new to SSAS, so I looked through the forum to see if this had been answered. I couldn't find it, but if its here please let me know.

I have a simple fact table of vendors and products purchased: Vendor, Part, Qty

Multiple Vendors can carry the same Part. I want to display the total Qty over all Vendors and the total Qty by a specific vendor for a specific Part purchased.

Example:

Vendor Part Qty TotalQTY

001 5x 3 10

002 4c 1 8

003 4c 7 8

004 6q 9 15

001 6q 6 15

What would be the best way to go about this?

Thanks in advance!

This 1st row of sample data is confusing, because it seems to be the only row for Part 5x; but maybe a row is missing:

Vendor Part Qty TotalQTY

001 5x 3 10

Anyway, assuming that there are Vendor and Part dimensions and a [Qty] "sum" measure, [TotalQty] could simply be like:

([Measures].[Qty], [Vendor].[Vendor].[All Vendors])

|||Thank you very much! I can see why you are an MVP.

No comments:

Post a Comment