Forum Discussion
Getting % by group (stacked column chart) - with filtered item
Hi guys.
Need help. My data is like this;
Week Product Qty Pass/Fail EXPECTED_RESULT
1 A 2 Fail 2/(2+3+4+6) = 13.3%
1 B 3 Fail 20%
1 C 4 Fail 26.7%
1 D 6 Pass .
2 A 3 Fail .
2 B 6 Fail .
2 C 2 Fail .
2 D 7 Pass .
3 A 4 Fail .
3 B 2 Fail
3 C 1 Fail
3 D 9 Pass
I am trying to create a stacked column chart where Axis=week, Legend=product, value=qty (in %). I filtered off the 'Pass'.
I would like to get the value 'qty' to be in percentage of product by week. but when i filter out 'Pass', the % is over the Fail only instead of Pass+Fail. How can i get the expected result as above.
Appreciate any help on this. Thanks!
As a MEASURE try this one
Measure = IF ( HASONEVALUE ( TableName[Qty] ), VALUES ( TableName[Qty] ) / CALCULATE ( SUM ( TableName[Qty] ), FILTER ( ALL ( TableName ), TableName[Week] = VALUES ( TableName[Week] ) ) ) )
5 Replies
- Zubair_Muhammad
Community Champion
If you need a calculated column try this
= TableName[Qty] / CALCULATE ( SUM ( TableName[Qty] ), FILTER ( TableName, TableName[Week] = EARLIER ( TableName[Week] ) ) )- Zubair_Muhammad
As a MEASURE try this one
Measure = IF ( HASONEVALUE ( TableName[Qty] ), VALUES ( TableName[Qty] ) / CALCULATE ( SUM ( TableName[Qty] ), FILTER ( ALL ( TableName ), TableName[Week] = VALUES ( TableName[Week] ) ) ) )this works nicely. i also found another way to get the same result using measure;
Percentage = sum(Table[Qty])/CALCULATE(sum(Table[Qty]),GROUPBY(Table,Table[Week]),all(Table))
Eric_ZhangMicrosoft Employee
You can try a measure as below. See more details in the attached pbix file.
perc = SUM ( yourTable[Qty] ) / CALCULATE ( SUM ( yourTable[Qty] ), ALLEXCEPT ( yourTable, yourTable[Week], yourTable[Pass/Fail] ) )Thanks. This is not what i want, but i've found the solution to it.
:)
