Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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?
Solved! Go to 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.
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")
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.
Solved! You rock!
User | Count |
---|---|
70 | |
70 | |
34 | |
23 | |
22 |
User | Count |
---|---|
96 | |
94 | |
50 | |
42 | |
40 |