How to view customer count by transaction count in OLAP using mdx or how to reverse view dimension and measures

0
votes

I have a sales transaction fact table and customer dimension table. I have transaction-count as a measure.

I want my report as:

No. of Time Transaction doing   Customers    Transactions
1                                10                10
2                                6                 12
3-6                              5                 ??

How can i achieve this in OLAP using mdx or even using excel but mainitaining pivot table structure.

1
excelssasmdxolapolap-cube
What is 3-6? Do you want customer count or distinct count of customers based on transaction fact table? – Danylo Korostil
3 - 6 means with 3 to 6 transcation count. Obviously distinct customer-count. Report answers this questions: how many customers has transaction count 1/2/3-6 and how many transaction they do. – Sandip
distinct count of customers based on transaction fact table. – Sandip
See i edited my question – Sandip

1 Answers

0
votes

You may create measures like this:

With
Member [Measures].[Customers with 1 transaction] as
Sum(
    existing [Customer].[Customer].[Customer].Members,
    IIF(
        [Measures].[TransactionCount] = 1,
        1,
        NULL
    )
)