Hi,
I'm rather new to Power BI and I think I need some help. What I want to do is to distinct count a column using a measure, but the column should also be present in the report! The thing is, if I just put in a few columns, the column "sales[customer]" AND the measure "Need Help With This = DISTINCTCOUNT(sales[customer])" it will show a "1" in every row for the measure column, as expected. The measure I want SHOULD show the DISTINCTCOUNT as if the column "sales[customer]" wasn't present in the report.
I know, that this explanation isn't really understandable, so let me paint you a picture real quick (table structure is made up, but the real data is structured similarly).
Let's say we have a table of "sales" data. In this table there are, among other columns the columns "article", "customer", "date", "country" and a few aggregatable values like "sales amount" and so on.
Before creating the report I create two filters; one for "calendar[year]" and one for "sales[country]". Keep that in mind! The data will be already filtered! There's a relationship between my calendar table (date, year, month, day, calendar week, ...) and my sales table.
The (already filtered!) table could look like this if you just dragged the columns into a tabular report:
article customer sales amount ...
===================================================
1 a 234 ...
1 b 4322 ...
1 c 23 ...
1 a 42 ...
2 b 34 ...
2 c 245 ...
2 c 564 ...
2 c 47 ...
3 g 354 ...
3 g 345 ...
3 g 778 ...
3 g 34 ...
Now, if I used SQL I'd create the desired resulting report using the following query (the bold line represents the measure I'm looking for):
SELECT s.article,
y.no_custmers as no_customers_per_article,
s.customer,
Count(s.*) AS no_of_rows_per_article_customer
FROM sales s
INNER JOIN (SELECT x.article,
Count(x.customer) AS no_custmers
FROM (SELECT article,
customer
FROM sales
GROUP BY article,
customer) x) y
ON s.article = y.article
group by s.article, s.customer
The resulting table would look like this:
article no_customers_per_article customer no_of_rows_per_article_customer
1 3 a 2
1 3 b 1
1 3 c 1
2 2 b 1
2 2 c 3
3 1 g 4
I know that I'm putting in redundant data into my report, but that's the point!
I'd like to be able to filter out articles, that only one customer buys, for example.
Does anyone have an idea how to create such a measure? I already tried SUMMARIZE, GROUPBY combined with a NATURALINNERJOIN, and so on. No luck.
I got pretty far by using
Need Help With This = CALCULATE(DISTINCTCOUNT(sales[customer])
, ALLEXCEPT(sales, sales[article], calendar[year], sales[country]))but this shows customers who haven't ordered anything in the filtered year, but in some other year! The value of the measure however is correct!?!
So the table report looks like this now:
article no_customers_per_article customer no_of_rows_per_article_customer
1 3 a 2
1 3 b 1
1 3 c 1
2 2 b 1
2 2 x (Blank)
2 2 y (Blank)
2 2 c 3
3 1 g 4
I found this solution here: http://community.powerbi.com/t5/Desktop/Distinct-Count-by-Coulmn/m-p/106693#M44752
The thing is, if the filtering would be static, then I'd already be done, because I'd create a calculated table or some calculated column with the filters already baked in. But we should be able to change the filtering from the report view! That's why I'm looking for a measure.
Best regards,
Chris
