How to summarize both distinct counts and averages based on group by column

[Mahi1827]

I have 3 columns store,ID,Days. i need to summarize the Total ids and average of days from Store column.
ID and Days are from Table 1 and store from table 2 and relation is enabled with two tables with ID.

my test data like

store   ID   Days
A        101 10
A       102  20
A       103  30
B       104  40
B       105  50
B       106  60
B       107  70

In result table i need to summarize based on store and my expected output like below

store TotalIDs   AvgDays
A        3              20
B        4              55

Total ids should distinctcounts and avgdays should take average from Days

So i have crated 2 measures group by store for totalids and Avg days like below but its not working as expected.

Total IDs=CALCULATE (DISTINCTCOUNT ( Table1[ID ),ALLEXCEPT (Table2,Table2[store] ))

AvgDays= AVERAGEX(Values(Table1[Days]),DISTINCTCOUNT(Table1[ID]),ALLEXCEPT (Table2,Table2[store]))

When i use above 2 mesures and add to table view with store column i am getting overall counts and averages only in one row but not individual counts like expected output.

So please help me with your inputs and suggestions on to get expected summary in table view..

Thanks for your help in advance.

Thanks,

mahi1827

[Anonymous]

@Mahi1827 -

Do you want to do that in visual or you want to transform the table like this-

Appreciate your kudos!! Mark my post as solution if it helps.

[Ashish_Mathur]

Hi,

These measures should work

Total ID = countrows(data)

Average days = average(data[days])

Hope this helps.

[Mahi1827]

Hi ,

Thanks for your time

Actually i have already  tried this same as provided measures  but i have got all summary data in id and days column like below.

Store Totalids   Avg days ( here its taking as overall data and overall average but not for individul records)

A         7            47.5

B         7            47.5

Myexpected output like

Store Total ids    avgdays

A        3                20

B         4               55

Please provide any possible solutions ..thank you.

Thanks,

Mahi

[Ashish_Mathur]

Hi,

My suggested measures should work.  Share the link from where i can download your PBI file.