Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Dear all,
I've got a problem with showing the duplicated values basesd on filters set in report. What I want to achive is to see how many Point of Sales are beeing providing products by more than 1 distributor based on choosen period & distributors.
Let's say I have table:
Year | Distributor | PoS |
2018 | C1 | PoS1 |
2018 | C1 | PoS2 |
2018 | C1 | PoS3 |
2018 | C1 | PoS4 |
2018 | C2 | PoS1 |
2018 | C2 | PoS3 |
2018 | C2 | PoS4 |
2018 | C2 | PoS5 |
2018 | C2 | PoS6 |
2018 | C3 | PoS3 |
2018 | C3 | PoS4 |
2018 | C3 | PoS6 |
2018 | C3 | PoS7 |
2018 | C4 | PoS1 |
2018 | C4 | PoS3 |
2018 | C4 | PoS5 |
2018 | C4 | PoS7 |
2018 | C4 | PoS8 |
2019 | C1 | PoS3 |
2019 | C1 | PoS4 |
2019 | C2 | PoS1 |
2019 | C2 | PoS3 |
2019 | C3 | PoS7 |
2019 | C4 | PoS1 |
2019 | C4 | PoS3 |
2019 | C5 | PoS1 |
2019 | C5 | PoS4 |
In total 2018 (without filters on Distributors) I would like to see:
PoS | Nb of Distributors |
PoS1 | 3 |
PoS2 | 1 |
PoS3 | 4 |
PoS4 | 3 |
PoS5 | 2 |
PoS6 | 2 |
PoS7 | 2 |
PoS8 | 1 |
which means that there are 8 uniques PoS, 2 of them are beeing provided products by 1 distributor, and 6 of them are provided by at least 2 distributors.
After setting filter on Distributor C1 & C2 the result would be like this:
PoS | Nb of Distributors |
PoS1 | 1 |
PoS2 | 1 |
PoS3 | 2 |
PoS4 | 2 |
PoS6 | 1 |
PoS7 | 1 |
so there are 6 unique PoS, 4 of them are provided by 1 distributor, and 2 them are provided by at least 2 distributors.
Similar with 3 different distributors like C1 & C3 & C4:
PoS | Nb of Distributors |
PoS1 | 2 |
PoS2 | 1 |
PoS3 | 3 |
PoS4 | 2 |
PoS5 | 1 |
PoS6 | 1 |
PoS7 | 2 |
PoS8 | 1 |
etc.
At first I whought about using calculated column to see which values are unique and which appear multiple times:
So i created mapping column to have date and PoS in one field:
MAP = 'Table1'[Year] & "_" & 'Table1'[PoS]
and then I checked the number of occurances in table
Chkduplicate = CALCULATE(COUNTA(Table1[MAP]);FILTER('Table1';'Table1'[MAP]=EARLIER('Table1'[MAP])))
This result was not something i want to see so it has to be done probbably using measures instead.
Many thanks,
Piotr
@Anonymous You can just change the "Distributor" field summarization to "Count(Distinct)" as show below in Values section. If you want to create measure then it will be. Shown both methods in the screenshots.
Test235 = DISTINCTCOUNT(Test235MeasureCount[Distributor])
Proud to be a PBI Community Champion
PattemManohar well this result in table is what i could alreadyachive, but what i could see already but what I actually want to see is how many PoS are provided by more than 1 distributor. Those tables were only for seeing summarized data from which I would like to see specific numbers.
So In case of setting filter on 2018 and all distributors there would be 8 PoS in Total, and 6 of them are provided by at lesast 2 disributors. not to see it by PoS. So in this case I would like to see number 6.
In case of filtering C1 and C2 it should show me number 3 - as 3 poS has products from at least 2 distributors.
In case of setting filter on C1, C2, C3 it would show number 4.
So it should count numbers >1 from this distinctcount measure, but when I try to make this measure it returns empty
count = CALCULATE([Test235];FILTER(Table1;[Test235]>1))
This is something I can't achive no matter what I try.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
104 | |
77 | |
67 | |
63 |
User | Count |
---|---|
144 | |
107 | |
105 | |
82 | |
69 |