Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
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))
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
143 | |
75 | |
62 | |
51 | |
47 |
User | Count |
---|---|
213 | |
81 | |
64 | |
60 | |
56 |