Forum Discussion
Solved
creating dynamic measure
Hello everyone,
please help how to create dynamic measure for below in power BI and power pivot data model in excel.
sample Data is below:
| Parent Customer | Customer Sales Group | Month | Value |
| 100135 | 3 | Sep-23 | |
| 100135 | Q20 | Sep-23 | 0.896675 |
| 100610 | 3 | Sep-23 | 0.15066 |
| 133107 | 3 | Sep-23 | 7.1619036 |
| 133107 | Q20 | Sep-23 | 1.2666331 |
| 135068 | 3 | Sep-23 | 0.56102 |
| 136289 | 3 | Sep-23 | |
| 136289 | 4 | Sep-23 | 0.36019 |
| 136289 | D34 | Sep-23 | 0.1833 |
| 136289 | P21 | Sep-23 | 1.22 |
| 190206 | 3 | Sep-23 | 0.41964 |
| 201810 | 3 | Sep-23 | 4.244085 |
| 201810 | Q20 | Sep-23 | 1 |
| 202151 | 2 | Sep-23 | 0.0714 |
| 202518 | 2 | Sep-23 | 0.8357227 |
| 202518 | Q20 | Sep-23 | 4.6553551 |
| 209291 | 2 | Sep-23 | 0.13626 |
| 100135 | 3 | Sep-24 | 1.2 |
| 100135 | Q20 | Sep-24 | 0.896675 |
| 100610 | 3 | Sep-24 | 0.15066 |
| 133107 | 3 | Sep-24 | 7.1619036 |
| 133107 | Q20 | Sep-24 | 1.2666331 |
| 135068 | 3 | Sep-24 | 0.56102 |
| 136289 | 3 | Sep-24 | 1 |
| 136289 | 4 | Sep-24 | 0.36019 |
| 136289 | D34 | Sep-24 | 0.1833 |
| 136289 | P21 | Sep-24 | 1.22 |
| 190206 | 3 | Sep-24 | 0.41964 |
| 201810 | 3 | Sep-24 | 4.244085 |
| 201810 | Q20 | Sep-24 | 1 |
| 202151 | 2 | Sep-24 | 0.0714 |
| 202518 | 2 | Sep-24 | |
| 202518 | Q20 | Sep-24 | 4.6553551 |
| 209291 | 2 | Sep-24 | 0.13626 |
Output Result 1 below:
| Parent Customer | Customer Sales Group | Sep-23 | Sep-24 | Grand Total |
| 100135 | 3 | 0.897 | 0.897 | 1.793 |
| 100135 | Q20 | 0.897 | 0.897 | 1.793 |
| 100610 | 3 | 0.151 | 0.151 | 0.301 |
| 133107 | 3 | 8.429 | 8.429 | 16.857 |
| 133107 | Q20 | 0.561 | 0.561 | 1.122 |
| 135068 | 3 | 0.000 | 1.000 | 1.000 |
| 136289 | 3 | 0.793 | 1.793 | 2.587 |
| 136289 | 4 | 1.153 | 1.153 | 2.307 |
| 136289 | D34 | 0.183 | 0.183 | 0.367 |
| 136289 | P21 | 1.220 | 1.220 | 2.440 |
| 190206 | 3 | 0.420 | 0.420 | 0.839 |
| 201810 | 3 | 5.244 | 5.244 | 10.488 |
| 201810 | Q20 | 1.000 | 1.000 | 2.000 |
| 202151 | 2 | 0.071 | 0.071 | 0.143 |
| 202518 | 2 | 5.491 | 4.655 | 10.146 |
| 202518 | Q20 | 4.655 | 4.655 | 9.311 |
| 209291 | 2 | 0.136 | 0.136 | 0.273 |
| Grand Total | 31.301 | 32.466 | 63.767 |
output Result 2 below:
| Customer Sales Group | Sep-23 | Sep-24 | Grand Total |
| 2 | 5.699 | 4.863 | 10.562 |
| 3 | 15.933 | 17.933 | 33.866 |
| 4 | 1.153 | 1.153 | 2.307 |
| D34 | 0.183 | 0.183 | 0.367 |
| P21 | 1.220 | 1.220 | 2.440 |
| Q20 | 7.113 | 7.113 | 14.226 |
| Grand Total | 31.301 | 32.466 | 63.767 |
please help how to create this.
Hi, dhanurjaya
Please check if this is the result you expect?
Sep-23 = CALCULATE(SUM('Table'[Value]),FILTER(ALLEXCEPT('Table','Table'[Parent Customer]),[Month].[Year]=2023))Sep-24 = CALCULATE(SUM('Table'[Value]),FILTER(ALLEXCEPT('Table','Table'[Parent Customer]),[Month].[Year]=2024))Please see the attached document.
Best Regards,
Community Support Team _Charlotte
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
1 Reply
Hi, dhanurjaya
Please check if this is the result you expect?
Sep-23 = CALCULATE(SUM('Table'[Value]),FILTER(ALLEXCEPT('Table','Table'[Parent Customer]),[Month].[Year]=2023))Sep-24 = CALCULATE(SUM('Table'[Value]),FILTER(ALLEXCEPT('Table','Table'[Parent Customer]),[Month].[Year]=2024))Please see the attached document.
Best Regards,
Community Support Team _Charlotte
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.