How to pivot within Power BI to do summary data calculations?

[dojobrady]

So I want to describe this data set, and then ask my question:

I uploaded an excel file to Power BI Desktop that is a download from my company's software.
It comprises of individual units (items) with their own unique ID's such as "123456" formatted as a whole number.

These units belong to pallets, such as "ABCDEFG" formatted as text. 

There are many units per pallet, and each unit can have a difference status. For context, we receive pallets, and we either resell the pallets or break down and sort the pallets for item level resell. In the data, you may see it formatted as such:

Unit    Pallet   Status

123     ABC    Incomplete

456     ABC    Not Checked In

789     ABC    Not Checked In

987     DEF    Sold

654     DEF    Sold

321     DEF    Listed

I am trying to look at truly "RAW" pallets in my data. This means the ENTIRETY of the pallet is "not checked in". In the example above, the pallet ABC is 67% not checked in, which means I can't count it. If item 123 was also "not checked in", pallet ABC would be eligible to be counted.

Today, to measure this, I have to pivot the excel sheet and make statuses the columns and pallet ID's the rows and do counts of item identifiers, then do the % of the statuses that are not checked in divided by the grand total, which then tells me which pallets are 100%. 

I don't even know where to begin on setting this up in PBI to do it for me. Can anyone help?

[Cmcmahan]

This very much depends on how you want to use this value.  If you just want a true/false column on each pallet, you can do that with a measure like this:

RAWPallet = IF(SELECTEDVALUE(Table3[Status]) = "Not Checked In", "Raw", "Not Raw")

I added a pallet GHI to the data, which has two Units in it, with status "Not Checked In"  to show a success case:

If you're trying to use this in another way (e.g. you still want the percentage that's raw), you may need a different measure

[jdbuchanan71 · Accepted solution]

You could add a calculated column that strings together all the status for a given pallet then filter it to only those where the only status is "Not Checked In"  I added another pallet with only that status to illustrate.

Pallet Full Status = 
VAR RowPallet = pallets[Pallet]
RETURN CALCULATE( CONCATENATEX ( SUMMARIZE(pallets,pallets[Pallet],pallets[Status]),pallets[Status],", "),ALL( pallets ), pallets[Pallet] = RowPallet)

Not sure if that is what you were looking for though.

[dojobrady]

Solved! You rock!