Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago
Solved

Need help setting up a logistics visual

I need to make a dashboard that shows what items that sells good in different warehouses.
so if warehouse L10 sells a certain item as item class code vk2 (the bad class) then i whant it
to show if there are any other warehouses that sells it in item class code vk1.

So
if warehouse and item equals vk2 then show what warehouses and item equals vk1

It would be great if i could choose a warehouse number from a filter
then i shows a list of items we have in vk2, then show in another list what warehouses
has that item in vk1.

Any surgestions on how im gona set this up? im still new to power bi and eager to learn.

 

Columns names "Warehouse number" "Item" "Item class"

 

Need Help
  • v-jiascu-msft's avatar
    v-jiascu-msft
    8 years ago

    Anonymous

     

    Hi Robin,

     

    That's OK. I found a solution. Please reference this file: https://1drv.ms/u/s!ArTqPk2pu-BkgRhSiy4U2C9zaXVG

    1. Create a new table "WH".

    WH =
VALUES ( 'Table1'[WareHouse Number] )

    2. Establish relationship.

    3. Create a measure.

    FinalMeasure =
VAR selectedWH =
    IF (
        HASONEVALUE ( WH[WareHouse Number] ),
        VALUES ( WH[WareHouse Number] ),
        BLANK ()
    )
VAR selectedWHvk2 =
    CALCULATETABLE (
        VALUES ( Table1[Item] ),
        FILTER (
            ALL ( 'Table1' ),
            'Table1'[Item Class] = 2
                && 'Table1'[WareHouse Number] = selectedWH
        )
    )
RETURN
    CALCULATE (
        IF (
            MIN ( 'Table1'[Item] ) IN selectedWHvk2
                && MIN ( 'Table1'[Item Class] ) = 1,
            1,
            0
        ),
        ALL ( WH )
    )

    4. Create several visuals.

    5. Filter the result. (Visual level filter)

     

     

     

     

     

     

     

     

     

     

     

     

     

    Note: 1. The slicers are from different tables. Don't mix up.

    2. All the columns are needed in the visual as the picture showed. 

     

    Best Regards!

    Dale

13 Replies

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    9 years ago

    Hi Anonymous,

     

    After a lot of test, I have some questions here.

    1. The vk2 and vk1 aren't in the sample. I am a little confused.

    2. If L10 sells 56854-5241 as class 1, you want to find out the warehouses that sells 56854-5241 but the class is 2. Am I right?

    3. Are the vk2 (2) and vk1 (1) static? Or you want to change them dynamically?

     

    Best Regards!

    Dale

    • Anonymous's avatar
      Anonymous
      Not applicable
      9 years ago

      Hi Dale and thank you very mutch for the assistance!

       

      1: Sorry i dide not call it VK1 and VK2 in the sample, i just wrote 1 and 2

      it should be Item class then either VK1 or VK2

       

      2: Its the oposite. If L10 sells the item in VK2 that means it dosent sell good. so then i whanto know what other warehouse sells the same item in VK1 witch is very good. its an overview so that a warehouse that dosent sell some items well in his region can send it to a region were it sells good

       

      VK1 Good

      VK2 baaad

      :)

       

      3: Im not sure what you mean here. The item classification is set in store for different warehouses unless changed if sales is on the rise

       

      - Robin

      • v-jiascu-msft's avatar
        v-jiascu-msft
        Icon for Microsoft Employee rankMicrosoft Employee
        9 years ago

        Anonymous,

         

        Hi Robin,

         

        You can try this measure.

        Measure =
VAR allSoldBad =
    CALCULATETABLE (
        VALUES ( Table1[Item] ),
        FILTER ( ALL ( Table1 ), 'Table1'[Item Class] = "vk2" )
    )
RETURN
    IF (
        MIN ( 'Table1'[Item] ) IN allSoldBad
            && MIN ( 'Table1'[Item Class] ) = "vk1",
        "GoodInAnother",
        "NoChanges"
    )

         

         

         

         

         

         

         

         

         

         

         

         

         

        Best Regards!

        Dale