question

erezh avatar image
erezh asked

problem with mdx grand total

hi , i'm using calculations in olap cube i wrote this query (mdx) but i don't get the grand total of "orders" when i use pivot table . case when([Measures].[orders]>0)then 1 else null end thanks erez
ssasmdxgroup-bysum
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Dave Morrison avatar image
Dave Morrison answered
Ahhh I see! right ok, you'll need to do something like the below, assuming you have a dimension called retailers and a dimension attribute called Retailer Name: COUNT(FILTER([Retailers].[Retailer Name], [Measures].[orders]>0)) This will give you the desired result. Alternatively I think you could even modify your original attempt to something like this and it work: SUM([Retailers].CurrentMember,CASE WHEN[Measures].[orders]>0)THEN 1 ELSE 0 END)
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Dave Morrison avatar image
Dave Morrison answered
Hi Erez, are you saying you've created this as a calculated measure in the cube? What are you trying to achieve with this query? If you're taking a sum over this calculated measure, what you're actually doing is a COUNT of the number of measures that are > 0. Is this what you intended?
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

erezh avatar image erezh commented ·
Hi Dave , yes , I created a calculated measure in the cube there is a hierarchies of customers that build like this area Wholesaler Retail i want to show the number of retailers who bought a selected item in each level. right now i only get the number 1 neer every cutomer who bought the item . in this case sum and count will get the same number becuase every retailer get the number 1 , even if he bought more then one time.
0 Likes 0 ·
Dave Morrison avatar image
Dave Morrison answered
Ok so if I understand correctly you need to put a SUM around this calculation in order to get what you require. This is providing you have set up all your dimension relationships correctly between the fact. The other approach is to use the COUNT() and NONEMPTY() functions something akin to: (Psudo code) COUNT(NONEMPTY(, [Measures].[orders]))
5 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

erezh avatar image erezh commented ·
Hi, I'm writhing this case when([Measures].[orders]>0)then COUNT (NONEMPTY(,[Measures].[orders] )) else null end but i get #VALUE! in all the parameters
0 Likes 0 ·
Dave Morrison avatar image Dave Morrison commented ·
Ah ok, try dropping the case statement : COUNT (NONEMPTY(,[Measures].[orders] ))
0 Likes 0 ·
erezh avatar image erezh commented ·
hi i use COUNT (NONEMPTY(,[Measures].[orders] )) but i still get #VALUE! in all the parameters it's importent to say that "orders" is alsow a culculate that give number of Buy back if the Retail bought 1 time he will get 0 if the Retail bought 2 time he will get 1 if the Retail bought 3 time he will get 2 and eatra...
0 Likes 0 ·
Dave Morrison avatar image Dave Morrison commented ·
I'm sorry I'm just not following your requirement, you keep adding these seemingly important caveats. Can you please give a full requirement of exactly what you're trying to achieve
0 Likes 0 ·
erezh avatar image erezh commented ·
O.K there is 2 calculations in the olap cube 1. The amount of repeat orders - this one is good (if the number is more then 0 ,it means that the Retail bought the product more then one time ) 2. Number of retailers who reorder - who Relies on the first calculate (the retailers that got number that big then 0 ) I'm trying to show in each level the sum of the retailers who bought more then one time the product . i hope that i'm more clearly . thanks again. i'm trying
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.