Dimensional Count Distinct

[jorgie]

hi to all and thank in advance for you time.

I have the following calculations

FieldA	FieldB	time		I need to
		count( distinct  [id] )	count (distinct total <[time]> [id])	????
ValueA		70	100	100
	TypeA	40	100	10
	TypeB	50	100	20
	TypeC	60	100	30
	TypeD	10	100	40
ValueB		100	100	100
	TypeA	10	100	10
	TypeB	20	100	20
	TypeC	30	100	30
	TypeD	40	100	40

I need to have the distinct total for any dimension I expand as in the example

Thank u!

[jorgie]

because there can be multiple lines for the time range i am looking and i need only one record.

[jorgie]

we are getting somewhere, but still please do read my comments below.

If dimension <time> is not used in code, I will have all the count ie 5000 and not the ones in the period I am looking into, ie 4500.

[Kushal_Chawda]

@jorgie  Could you please provide expected output with Numbers based on sample data? 

[jorgie]

I attach a less dummy data set

the result i have based on the code that is on header is the following

for example for each type I want to get the total of id2.

more specific for every id2 that is 10, the value i want to have is for the first week 281, and 296 for the other week. no mater the type.

[Kushal_Chawda]

@jorgie  with count(distinct value) you are already getting 281 & 296 for week 1 & week2 what next do you want?

[jorgie]

I need the total. The total based on each row value.

I want to  get % of total calculation. Is ready to use in power data model pivot excel.

I just need to have the values I described in order to have the demanded calculation 

[M_B]

Did you try something like 'Aggr(nodistinct Count(total value), type, time)'?

Also, if I noticed correctly you are using a pivot table. I am not sure if the values specified in the Aggr() function I gave will "scale" with expanded or collapsed fields/rows.

If I can try to clarify things about your requirement for others, I will use my example:

I had a table looking like this:

Center	Type	Value
-	-	6000
1	Status1	1500
1	Status2	300
1	Status3	100
1	Status4	75
1	Status5	25
2	Status1	1300
2	Status2	400
2	Status3	150
2	Status4	80
2	Status5	70
3	Status1	1800
3	Status2	150
3	Status3	30
3	Status4	20
3	Status5	10

What I wanted was to display the % of each center to its own total, so I used the 'Count(value)/Aggr(nodistinct Count(value), Center)' and got the following:

Center	Type	Value	Percentage per center
-	-	6000	300%
1	Status1	1500	75%
1	Status2	300	15%
1	Status3	100	5%
1	Status4	75	37.5%
1	Status5	25	12.5%
2	Status1	1300	65%
2	Status2	400	20%
2	Status3	150	7.5%
2	Status4	80	4%
2	Status5	70	3.5%
3	Status1	1800	90%
3	Status2	150	7.5%
3	Status3	30	1.5%
3	Status4	20	1%
3	Status5	10	0.5%

Yes, the total percentage is 300% because each center's percentage is calculated "internally" if you will.

Now this can work with a straight table and using 1 dimension. I am now sure how to integrate it into a pivot table with more dimensions. I hope this helps others understand your requirement.

If my example is not correct for your use case please give us some feedback.

[jorgie · Accepted solution]

if( dimensionality()=2, count( distinct total <[FieldB], [time]> [id]), count( distinct total <[time]> [id]) )