MDX : Sum of distinct values based on a specific attribute

• Question

• So I have this problem in SSAS that I wasn't able to solve at all(this been 1 month already ...)

I have a fact that have different links to multiple dimensions, one of them have a hierarchy. (Division->Sub-division->product), In this Fact that I have AMOUNT I want to calculate. The data I have in my fact table is on the product level. I want to create a measure that will be able to do Sum based on the Sub-division level. So my data look like this :

``````| FACT_ID | DIM_Product   | DIM_Sub-Division | AMOUNT  |
|---------|---------------|------------------|---------|
| 1       | P1            | A                | 100     |
| 2       | P2            | A                | 100     |
| 3       | P3            | B                | 200     |
| 4       | P4            | B                | 200     |
| 5       | P5            | C                | 300     |
|---------|---------------|------------------|---------|

``````

The idea is to have distinct sum of the amounts based on the sub divion level to create an average based on the sub division level (sub disivison distinct sum / distinct count of sub-division). I know that amounts per sub-division are the same. The agragation type of the Amount at the moment is Sum, so the total I get is 900 instead of 700.

The result I want to have is this :

`````` | DIM_Sub-Division | AMOUNT  |
|------------------|---------|
| A                | 100     |
| B                | 200     |
| C                | 300     |
|------------------|---------|``` TOTAL | 600     |```

Any idea to solve this problem ?

Thanks !

• Edited by Tuesday, January 9, 2018 2:57 PM
Tuesday, January 9, 2018 2:27 PM

All replies

• So do you ever need to do a non-distinct sum on this AMOUNT column? If not that implies that you probably have a data granularity issue and the best way of solving this is to create a separate fact table at the Sub-Division grain for this amount.

Tuesday, January 9, 2018 10:18 PM
• I suspect Darren is right about you having a granularity issue. So you might want to redesign your cube.

However, it is possible to group by distinct measure values, if you really need to do that. Here is a similar example that groups by measure value. http://richardlees.blogspot.com.au/2010/03/mdx-group-by-measure-range-dynamically.html

Hope that helps,

Richard

Tuesday, January 9, 2018 11:41 PM
• Hi Vooriden,

Assuming the first table is your real fact table,then the sum report for Sub-division level should looks like something below, and it should be the correct results:

DIM_Sub-Division | AMOUNT  |
|------------------|---------|
| A                | 200     |
| B                | 400     |
| C                | 300     |
|------------------|---------|

TOTAL             | 900     |

Please do consider the solution provide by Darren for the data granularity issue.

Best Regards
Willson Yuan
MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

Wednesday, January 10, 2018 3:05 AM