Forum Discussion
Distinct count with filter and total
- 4 years ago
Hi, Dtrain
I have been busy recently, sorry to reply you late. You need to use sumx() and summarize() function to calculate toal.
Like this:
Total In-store = SUMX ( SUMMARIZE ( 'Table', [Date], "a", COUNTROWS ( ( DISTINCT ( CALCULATETABLE ( UNION ( CALCULATETABLE ( DISTINCT ( 'Table'[AcceptedByCusId] ), FILTER ( 'Table', 'Table'[AcceptedByCusId] <> BLANK () ) ), DISTINCT ( 'Table'[CreatedByCusId] ) ), 'Table'[Auction Type] = "In-Store" ) ) ) ) ), [a] )Other measures are similar, you can refer to my sample below.
Did I answer your question ? Please mark my reply as solution. Thank you very much.
If not, please feel free to ask me.
Best Regards,Community Support Team _ Janey
I keep on getting this error when creating a table. What am I doing wrong?
- v-janeyg-msft4 years agoCommunity Support
Hi, Dtrain
I have been busy recently, sorry to reply you late. You need to use sumx() and summarize() function to calculate toal.
Like this:
Total In-store = SUMX ( SUMMARIZE ( 'Table', [Date], "a", COUNTROWS ( ( DISTINCT ( CALCULATETABLE ( UNION ( CALCULATETABLE ( DISTINCT ( 'Table'[AcceptedByCusId] ), FILTER ( 'Table', 'Table'[AcceptedByCusId] <> BLANK () ) ), DISTINCT ( 'Table'[CreatedByCusId] ) ), 'Table'[Auction Type] = "In-Store" ) ) ) ) ), [a] )Other measures are similar, you can refer to my sample below.
Did I answer your question ? Please mark my reply as solution. Thank you very much.
If not, please feel free to ask me.
Best Regards,Community Support Team _ Janey
- Dtrain4 years agoHelper I
Still giving me the error when trying to inserting the table. any other suggestion?
- v-janeyg-msft4 years agoCommunity Support
- Dtrain4 years agoHelper I
Thanks you so much for your support.