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,
I'm calculating the weight on the week divided by the weight of the month, and my KPI is the average of their percentages by month. There is anyway that I could do this as measure that will allow me filter by plant?
Table:
Year | Plant | Month | Week | Weight | Date | Concat |
2019 | PMRI | 9 | W1 | 1299,695 | 01/09/2019 | W1September |
2019 | PMRI | 9 | W2 | 1462,354 | 01/09/2019 | W2September |
2019 | FACF | 10 | W1 | 901,005 | 01/10/2019 | W1October |
2019 | LDCI | 9 | W4 | 2961,658 | 01/09/2019 | W4September |
2019 | PACI | 11 | W1 | 8665,756 | 01/11/2019 | W1November |
2019 | PACI | 9 | W1 | 8665,756 | 01/09/2019 | W1September |
2019 | BMCI | 10 | W1 | 3739,291 | 01/10/2019 | W1October |
2019 | PACI | 10 | W1 | 8665,756 | 01/10/2019 | W1October |
2019 | PACI | 10 | W3 | 5548,825 | 01/10/2019 | W3October |
2019 | PACI | 12 | W1 | 8665,756 | 01/12/2019 | W1December |
2019 | BMCI | 12 | W1 | 3739,291 | 01/12/2019 | W1December |
2019 | PACI | 9 | W4 | 1743,095 | 01/09/2019 | W4September |
2019 | PACI | 10 | W4 | 1743,095 | 01/10/2019 | W4October |
2019 | BMCI | 9 | W1 | 3739,291 | 01/09/2019 | W1September |
2019 | BMCI | 10 | W4 | 1555,722 | 01/10/2019 | W4October |
2019 | LDCI | 10 | W4 | 2961,658 | 01/10/2019 | W4October |
2019 | PACI | 11 | W3 | 5548,825 | 01/11/2019 | W3November |
2019 | PACI | 12 | W3 | 5548,825 | 01/12/2019 | W3December |
2019 | PACI | 10 | W2 | 4448,179 | 01/10/2019 | W2October |
2019 | LDCI | 11 | W4 | 2961,658 | 01/11/2019 | W4November |
2019 | VLCI | 10 | W4 | 73,602 | 01/10/2019 | W4October |
2019 | VLCI | 11 | W4 | 73,602 | 01/11/2019 | W4November |
2019 | VLCI | 10 | W3 | 62,526 | 01/10/2019 | W3October |
2019 | LDCI | 9 | W3 | 8200,935 | 01/09/2019 | W3September |
2019 | PECI | 9 | W3 | 84,893 | 01/09/2019 | W3September |
2019 | PECI | 10 | W3 | 84,893 | 01/10/2019 | W3October |
2019 | PECI | 11 | W4 | 147,708 | 01/11/2019 | W4November |
2019 | PMCI | 12 | W1 | 1182,982 | 01/12/2019 | W1December |
2019 | PMRI | 11 | W4 | 946,721 | 01/11/2019 | W4November |
2019 | PECI | 10 | W2 | 203,629 | 01/10/2019 | W2October |
2019 | PMCI | 9 | W1 | 1182,982 | 01/09/2019 | W1September |
2019 | VLCI | 9 | W3 | 62,526 | 01/09/2019 | W3September |
2019 | VLCI | 9 | W1 | 143,143 | 01/09/2019 | W1September |
2019 | VLCI | 10 | W2 | 191,504 | 01/10/2019 | W2October |
2019 | BMCI | 10 | W3 | 7783,261 | 01/10/2019 | W3October |
2019 | BMCI | 11 | W4 | 1555,722 | 01/11/2019 | W4November |
2019 | BMCI | 12 | W3 | 7783,261 | 01/12/2019 | W3December |
2019 | BMCI | 11 | W2 | 1057,279 | 01/11/2019 | W2November |
2019 | BMCI | 12 | W2 | 1057,279 | 01/12/2019 | W2December |
2019 | VLCI | 9 | W2 | 191,504 | 01/09/2019 | W2September |
2019 | PECI | 9 | W2 | 203,629 | 01/09/2019 | W2September |
2019 | VLCI | 12 | W4 | 73,602 | 01/12/2019 | W4December |
2019 | PECI | 9 | W4 | 147,708 | 01/09/2019 | W4September |
2019 | PECI | 9 | W1 | 148,464 | 01/09/2019 | W1September |
Desire output:
SUM(Weight of Week "1" on January)/SUM(Weight on January) %
SUM(Weight of Week "2" on February)/SUM(Weight on February) %
SUM(Weight of Week "3" on March)/SUM(Weight on March) %
Example:
Week | Sep | Oct | Nov | Dec | Jan | Feb | KPI |
W1 | 34% | 33% | 32% | 35% | 18% | 23% | 29% |
W2 | 28% | 27% | 27% | 29% | 29% | 28% | 28% |
W3 | 26% | 28% | 29% | 31% | 39% | 39% | 32% |
W4 | 12% | 13% | 12% | 6% | 15% | 11% | 11% |
Solved! Go to Solution.
Hi
I solved it with 2 measures (which probably can be simplified to 1)
Measure = VAR __monthTotal = CALCULATE( SUM( 'Table'[Weight]); ALLEXCEPT( 'Table'; 'Table'[Month]) ) RETURN DIVIDE( SUM( 'Table'[Weight]); __monthTotal; 0 ) Measure 2 = IF( ISFILTERED( 'Table'[Month]); [Measure]; AVERAGEX( SUMMARIZE( 'Table'; 'Table'[Month]; 'Table'[Week]; "measure"; [Measure] ); [measure] ) )
If this works then please accept it as the solution, kudos is also appreciated.
Hi
I solved it with 2 measures (which probably can be simplified to 1)
Measure = VAR __monthTotal = CALCULATE( SUM( 'Table'[Weight]); ALLEXCEPT( 'Table'; 'Table'[Month]) ) RETURN DIVIDE( SUM( 'Table'[Weight]); __monthTotal; 0 ) Measure 2 = IF( ISFILTERED( 'Table'[Month]); [Measure]; AVERAGEX( SUMMARIZE( 'Table'; 'Table'[Month]; 'Table'[Week]; "measure"; [Measure] ); [measure] ) )
If this works then please accept it as the solution, kudos is also appreciated.
Hey @Anonymous ,
It worked with this small adaptation:
Measure = VAR __monthTotal = CALCULATE( SUM( 'Table'[Weight]); ALLEXCEPT( 'Table'; 'Table'[Month];'Table'[Plant]) ) RETURN DIVIDE( SUM( 'Table'[Weight]); __monthTotal; 0 ) Measure 2 = IF( ISFILTERED( 'Table'[Month]); [Measure]; AVERAGEX( SUMMARIZE( 'Table'; 'Table'[Month]; 'Table'[Week]; "measure"; [Measure] ); [measure] ) )
Thanks!
Hi @Anonymous
It's working when Looking at Macro, but when it is filtered by Plant, the total is divided by the total of the month, I think that I didn't explain this before, but what I want is that when is filtered by Plant the KPI is recalculated by:
SUM(Weight produced by the plant on the week)/Sum(Weight produced by the plant on the Month).
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 |
---|---|
109 | |
98 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
111 | |
92 | |
84 | |
66 |