Forum Discussion
Getting some right and some wrong values in a measure because of unpivoted data
There is a closing inventory and Material code in this table.
From the above condition, I have got the below table.
This is the table including binary values. Columns are weeks.
From the above dax, calculated the initial 6 weeks sum.
As you can see Material code 1043 is having sum 2 which is correct.
Getting correct values for some Material Code but cannot get correct values for every Material Code.
For example, check the Material Code 11002245
The initial 6 week's Sum should be 5 but it displays 4.
For material code 1122, the sum should be 6 but it displays 32. It is because of unpivoted data.
How resolve this issue occurring by unpivoted columns.
Can anyone help with this?
Hi Anonymous ,
You can try using the following dax.
Measure = var _minweek= MINX( FILTER(ALL('HUL (3)'),'HUL (3)'[Customer Material Code]=MAX('HUL (3)'[Customer Material Code])&&ISBLANK([Alert])=FALSE()),[WeekNo]) return SUMX( FILTER( ALL('HUL (3)'),'HUL (3)'[Customer Material Code]=MAX('HUL (3)'[Customer Material Code])&&'HUL (3)'[WeekNo]>=_minweek&&'HUL (3)'[WeekNo]<_minweek+5),[Alert])While calculating the data you provided, I noticed a small discrepancy that I would like to share with you, I found that in 11002245, if week>=29 and week<34,then the 11002245 value is 29:1 + 30:1 + 31:1 + 32:1+33:0=4
If it doesn't meet your desired outcome, can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data. We can better understand the problem and help you
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 Replies
- lbendlin
Super User
Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information or anything not related to the issue or question.
If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
Please show the expected outcome based on the sample data you provided.
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523 Hi Anonymous ,
You can try using the following dax.
Measure = var _minweek= MINX( FILTER(ALL('HUL (3)'),'HUL (3)'[Customer Material Code]=MAX('HUL (3)'[Customer Material Code])&&ISBLANK([Alert])=FALSE()),[WeekNo]) return SUMX( FILTER( ALL('HUL (3)'),'HUL (3)'[Customer Material Code]=MAX('HUL (3)'[Customer Material Code])&&'HUL (3)'[WeekNo]>=_minweek&&'HUL (3)'[WeekNo]<_minweek+5),[Alert])While calculating the data you provided, I noticed a small discrepancy that I would like to share with you, I found that in 11002245, if week>=29 and week<34,then the 11002245 value is 29:1 + 30:1 + 31:1 + 32:1+33:0=4
If it doesn't meet your desired outcome, can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data. We can better understand the problem and help you
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
