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.
Hello!
Lets imagine that we have a dataset like this:
Company | Year | Num Of Sales |
a | 2017 | 50 |
b | 2017 | 60 |
c | 2017 | 70 |
d | 2017 | 80 |
e | 2017 | 90 |
f | 2017 | 100 |
a | 2018 | 45 |
b | 2018 | 55 |
c | 2018 | 65 |
d | 2018 | 30 |
e | 2018 | 20 |
f | 2018 | 10 |
a | 2019 | 100 |
b | 2019 | 20 |
c | 2019 | 80 |
d | 2019 | 45 |
e | 2019 | 30 |
f | 2019 | 15 |
And we have to create a matrix with a set of columns representing only Companies having a 75% of market at a selected period (for example, for 2017 it must be only c, d, e and f columns in the matrix)
On changing the period the column set must be automatically changed.
Is this possible?
Thank you!
Solved! Go to Solution.
Hi @Riff ,
We can use the following measure to archive your requirement:
Measure = VAR totalsales = SUMX ( ALLSELECTED ( 'Table' ), [Num Of Sales] ) VAR t = ADDCOLUMNS ( ALLSELECTED ( 'Table' ), "Index", RANKX ( ALLSELECTED ( 'Table' ), CALCULATE ( SUM ( 'Table'[Num Of Sales] ) ), , ASC, DENSE ) ) VAR t2 = ADDCOLUMNS ( t, "total", SUMX ( FILTER ( SELECTCOLUMNS ( t, "I", [Index], "s", [Num Of Sales] ), [Index] <= [I] ), [s] ) ) RETURN IF ( RANKX ( ALLSELECTED ( 'Table' ), CALCULATE ( SUM ( 'Table'[Num Of Sales] ) ), , ASC, DENSE ) >= MAXX ( FILTER ( t2, [total] >= totalsales * 0.75 ), [Index] ), SUM ( 'Table'[Num Of Sales] ), BLANK () )
BTW, pbix as attached.
Best regards,
Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Riff ,
We can use a measure as value to meet your requirement:
Measure = VAR sumr = COUNTROWS ( ALLSELECTED ( 'Table' ) ) RETURN IF ( RANKX ( ALLSELECTED ( 'Table' ), CALCULATE ( SUM ( [Num Of Sales] ) ), SUM ( 'Table'[Num Of Sales] ), ASC, DENSE ) > ROUNDUP ( 0.25 * sumr, 0 ), SUM ( 'Table'[Num Of Sales] ), -- Or Other Value You Want To Show BLANK () )
BTW, pbix as attached.
Best regards,
Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hello, @v-lid-msft ! Thank you for you answer.
If i got it right, your solve shows the 75% of total count of companies. I mean, if we have 8 companies, your solve will always show top 6 of them. Probably I did not explain the task enough clear. I need a list of companies that have 75% of sales at market. So for example if we have 6 companies where one of them has 75 sales at year and every of other 5 companies has only 5 sales (total for all of 6 is 100), then we have to get only 1st company because it has 75% of market alone.
Thank you!
Hi @Riff ,
We can use the following measure to archive your requirement:
Measure = VAR totalsales = SUMX ( ALLSELECTED ( 'Table' ), [Num Of Sales] ) VAR t = ADDCOLUMNS ( ALLSELECTED ( 'Table' ), "Index", RANKX ( ALLSELECTED ( 'Table' ), CALCULATE ( SUM ( 'Table'[Num Of Sales] ) ), , ASC, DENSE ) ) VAR t2 = ADDCOLUMNS ( t, "total", SUMX ( FILTER ( SELECTCOLUMNS ( t, "I", [Index], "s", [Num Of Sales] ), [Index] <= [I] ), [s] ) ) RETURN IF ( RANKX ( ALLSELECTED ( 'Table' ), CALCULATE ( SUM ( 'Table'[Num Of Sales] ) ), , ASC, DENSE ) >= MAXX ( FILTER ( t2, [total] >= totalsales * 0.75 ), [Index] ), SUM ( 'Table'[Num Of Sales] ), BLANK () )
BTW, pbix as attached.
Best regards,
Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
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 |
---|---|
110 | |
97 | |
78 | |
63 | |
55 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |