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.
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!
Solved! Go to Solution.
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] ) ) ) )
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.
🙂
If you need a calculated column try this
= TableName[Qty] / CALCULATE ( SUM ( TableName[Qty] ), FILTER ( TableName, TableName[Week] = EARLIER ( TableName[Week] ) ) )
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))
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 |
---|---|
112 | |
100 | |
80 | |
64 | |
57 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |