Getting some right and some wrong values in a measure because of unpivoted data

[Anonymous]

There is a closing inventory and Material code in this table.

Alert = IF([Closing Inventory]<0,1,0)

From the above condition, I have got the below table.

This is the table including binary values. Columns are weeks.

Sum of First 6 Weeks Alert =

VAR CurrentMaterialCode = SELECTEDVALUE('HUL (3)'[Customer Material Code])

VAR FirstWeek = CALCULATE(

    MIN('HUL (3)'[WeekNo]),

    FILTER(

        ALL('HUL (3)'),

        'HUL (3)'[Customer Material Code] = CurrentMaterialCode

    )

)

VAR WeeksToSum = FILTER(

    ALL('HUL (3)'),

    'HUL (3)'[Customer Material Code] = CurrentMaterialCode

        && 'HUL (3)'[WeekNo] >= FirstWeek

        && 'HUL (3)'[WeekNo] < FirstWeek + 5

)

RETURN

SUMX(WeeksToSum, 'HUL (3)'[Alert])

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?

[Anonymous · Accepted solution]

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.

[lbendlin]

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-...

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/1447...