CALCULATE help

[aaditya2000]

Hello:

I want the COUNT of Batch# for all storename for each customer. below is the screenshot.

I am looking for something like this.

XYZ-->ALL storename-->Count of all Batch#

MEASURE = 4 (all stores) X 16 (count of batch)

Need help how i can do that.

Any help is greatly appreciated.

thanking in advance.

Thanks

[Anonymous]

@aaditya2000,

Firstly, select the three columns in TABLE-1, then select “Remove Duplicates”.


Secondly, create relationship between TABLE-1 and TABLE-2 using ID field.

Thirdly, create the following measures in TABLE-1 and create table visual.

Count of stores = DISTINCTCOUNT('TABLE-1'[storename])
Count of batch = CALCULATE(COUNT('TABLE-2'[Batch1#]))
Measure 2 = [Count of stores]*[Count of batch]


Regards,

[aaditya2000]

Hello:

Thank you for your reply. The solution you provided is doable, but i amnot seeing filtering cutomer in the measure calculations.

I see customer names in table but not in measure calculations.

Is it i am missing something over here?

Please help.

Thanking in advance.

Thanks

[Anonymous]

@aaditya2000,

When you add customer field in table, the measure is calculated by customer. You can remove customer field from the above table visual and you will see the measure result is different.

Regards,
Lydia

[dilumd]

Hi,

Below function will consider all columns in your data table except Batch # column,

Count of Raws = CALCULATE(COUNTROWS(CountRaws),ALLEXCEPT(CountRaws,CountRaws[Batch#]))

However, if you use below formula with ALL function it will ignore all filters inside the table.

Count of Raws = CALCULATE(COUNTROWS(CountRaws),ALL(CountRaws[Batch#]))

[aaditya2000]

Thank you for your reply. want to know how i can do that where based on customer, i want to total count = total stores X count of batch.

for e.g = 4 stores X count of batch (4)

           = 16.

thanks

[dilumd]

you can use SUM or SUMX functions based on your ultimate goal.