Count rows in matrix

[JamesQi]

Hi all,

I need help in DAX coding.

In my company, multiple SKUs could share a same PO number, however, some SKUs are produced and finished earlier than others. So, the PO status tracking file I got from our ERP system looks like this

For example PO ID B0602055 and B1605429 have one SKU finished while another one is pending production.

I need to find out how many POs are complete fininshed, (like B1605385 and B1605410). I tried a few search and couldn’t find a hit. My closed approach ends up like this.

PO.Countfinished =
COUNTAX( FILTER(SUMMARIZE(F1_Manufacture_PO, ‘D1_Support_BusinessUnit’[BUID],F1_Manufacture_PO[PO_ID],F1_Manufacture_PO[Unfinished QTY] ), [PO.Unfinished QTY] = 0 ), [PO.Unfinished QTY]= 0)

F1_Manufacture_PO is the PO fact table, it is filtered by BU support table D1_Support_BusinessUnit.

My DAX code count each row of unfinished QTY = 0 instead of aggregate then count, could some expert help me on this?

Original PO file from our ERP system is attached.

Thank you.

James

PO File.zip (12.7 KB)

[Harsh]

Hello @JamesQi,

Thank You for posting your query onto the Forum.

To achieve the scenario where you’ve two completed PO’s i.e. PO No. B1605385 and PO No. B1605410. I followed the steps as provided below -

1). Added a Calculated Column in this case to find out the Total No. of Occurences of PO No.'s where it’s more than 1. Below is the DAX provided for the reference -

Occurence of PO's = 
COUNTX (
    FILTER ( Sheet1 , 
        EARLIER ( Sheet1[PO ID] ) = Sheet1[PO ID] ),
    Sheet1[PO ID]
)

2). Written a DAX measure based on the condition/logic which you’ve specified in your post. Below is the measure provided for the reference -

Completed PO's = 
SWITCH( TRUE() , 
    SELECTEDVALUE( Sheet1[Occurence of PO's] ) > 1 , 0 , 
    SELECTEDVALUE(  Sheet1[SKU QTY] ) = SELECTEDVALUE( Sheet1[Unfinished QTY] ) , 
    0 ,
    1 )

3). And finally, wrote a measure that calculates the “Total No. Of PO’s completed” at a Grand Total level. Below is the measure provided for the reference -

Total Completed PO's = 
SUMX(
    SUMMARIZE(
        Sheet1 , 
        Sheet1[Business Unit ID] , 
        Sheet1[Customer ID] , 
        Sheet1[PO ID] , 
        Sheet1[SKU] , 
        Sheet1[Order Date] , 
        "@Totals" , 
        [Completed PO's] ) , 
    [@Totals]
)

So finally after writing these 3 small formulas you’ll get the results that you’re trying to achieve. Below is the screenshot provided for the reference -

I’m also attaching the working of the PBIX file for the reference purposes.

Hoping you find this useful and meets your requirements that you’ve been looking for.

Thanks and Warm Regards,
Harsh

Completed PO’s - Harsh.pbix (22.1 KB)

[Harsh]

Hello @JamesQi,

I was thinking about how to make no use of calculated columns and convert them into the measures and then I realized that Sam Sir has already created a video on this topic and it almost went off my mind.

So rather then following the first two steps I converted them into one single step i.e. a measure and then created one final measure to fix the totals which was my third step. Below is the measure provided for the reference -

Completed PO's - 2 = 
VAR _Current_PO = SELECTEDVALUE( Sheet1[PO ID] )

VAR _Occurence_of_PO =
COUNTX(
    FILTER( ALL( Sheet1 ), Sheet1[PO ID] = _Current_PO ),
        Sheet1[PO ID] )

VAR _Condition = 
SELECTEDVALUE( Sheet1[SKU QTY] ) = SELECTEDVALUE( Sheet1[Unfinished QTY] )

RETURN
SWITCH( TRUE() , 
    _Occurence_of_PO > 1 , 0 , 
    _Condition , 0 , 
    1 )

I’m also attaching the updated version of the working PBIX file as well as the providing the link of the video below which I remembered/referred to convert my calculated column into the measure. Also providing the link of a post which our expert @Greg had created about how to fix the totals. (I forgot to provide the link of that post in my previous post.)

Hoping you find this useful and meets your requirements that you’ve been looking for.

Thanks and Warm Regards,
Harsh

Completed PO - Harsh v2.pbix (22.6 KB)

Calculate The Last Or Max Value Within A Table Of Data Using DAX In Power BI

Fix Incorrect Totals

When using a measure column in a visual, it is not uncommon in Power BI to find that the totals are incorrect. The Fix Incorrect Totals DAX pattern can be used to correct this issue. When a DAX measure is providing the correct detail value it often, unfortunately, provides an incorrect value for the total row. This is due to there being no evaluation context for the total row. For example, when comparing the [Total Sales] to the [Sales LY] to find the minimum for a customer using the simple DAX…

[Anurag]

Hi,

If your unfinished column always going to have 0 to indicate the completion then you can use the below dax also otherwise the solution provide by @Harsh is good to go.

completed or not =
VAR current_PO =
SELECTEDVALUE ( Table1[PO ID] )
VAR sum_ =
SUMX (
FILTER ( ALL ( Table1 ), Table1[PO ID] = current_PO ),
Table1[Unfinished QTY]
)
VAR result =
IF ( sum_ > 0, 0, 1 )
VAR tb =
SUMMARIZE ( Table1, Table1[PO ID], “@Sum”, SUM ( Table1[Unfinished QTY] ) )
VAR tb2 =
ADDCOLUMNS ( tb, “@con”, IF ( [@Sum] > 0, 0, 1 ) )
RETURN
IF ( ISINSCOPE ( Table1[Business Unit ID] ), result, SUMX ( tb2, [@con] ) )

Attaching the working Pbix for your refrence.
PO File.pbix (43.8 KB)

[EnterpriseDNA]

Thank you so much for the very detailed answer @Harsh and @Anurag

Hi @JamesQi did the response provided above help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query.

I hope that you are having a great experience using the Support Forum so far. Kindly take time to answer the Enterprise DNA Forum User Experience Survey, we hope you’ll give your insights on how we can further improve the Support forum. Thanks!

[JamesQi]

Hi all,

Thanks for your help on logic and DAX coding.

I am testing these logics, so far I need to dig in the logic a little bit more as my PO database is complex than the sample. It doesn’t work by just sample copy the code.

Once I figure out I will post here. Or I may have to come back for another help.

Cheers.

James

[EnterpriseDNA]

We hope the answers helped you @JamesQi

If not, how far did you get and what kind of help you need further?

If yes, kindly mark as solution the answer that solved your query.

[JamesQi]

Thanks all for your help and sorry for the long delay as I tried to solve the problem using earlier code but ended up without any luck (I have limited understanding in EARLIER so it should be my problem)

So far only @Anurag solution works for my model, so a big thank you Anurag. However, Anurag’s solution is beyond my understanding of DAX at this moment so I will try my best to understand the logic behind it.

Here’s the result after loading all the POs.

In my real world case there could be 1-15 lines of SKUs under the same PO number. My PO table also filtered by BU ID, date and Customer table as the dimension tables. It seemed that @Harsh solution can’t be filtered by dimension tables and the PO with more than 2 lines. Show in the screenshots.

Thank you all again.