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

Anonymous's avatar
Anonymous
Not applicable
9 years ago

Yes it dose work, but is it possible to make it show what warehouse number its good at?

The example i made is just a few rows. but the actual data is 10 of thousands of item numbers

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

Hi Anonymous,

 

In the sample, we can use this measure to list the warehouses in one row. One precondition: one item only has one class in a special warehouse.

 

Measure 2 =
VAR allSoldBad =
    CALCULATETABLE (
        VALUES ( Table1[Item] ),
        FILTER ( ALL ( Table1 ), 'Table1'[Item Class] = "vk2" )
    )
RETURN
    CONCATENATEX (
        FILTER (
            SUMMARIZE (
                'Table1',
                'Table1'[WareHouse Number],
                'Table1'[Item],
                "IfGood", IF (
                    MIN ( 'Table1'[Item] ) IN allSoldBad
                        && MIN ( 'Table1'[Item Class] ) = "vk1",
                    1,
                    0
                )
            ),
            [IfGood] = 1
        ),
        [WareHouse Number],
        "-"
    )

 

 

 

 

 

 

 

 

Or use the visual level filters.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Best Regards!

Dale

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    Hi Dale

    I think we are missunderstanding each other, this dosent seam to work for me =/
    I have made a pivot so far, and got a very clunky list over item numbers with item classification.

    Here we can see all the warehouse number sorted by item classification 1 - 2
    and we can see what item is in item class 1 in 1 warehouse and item class 2 in the other.
    So my question to you is,
    Can we filter this somehow? so we only see the item numbers that are actualy in stock at 2 different warehouses with 2 different classifications?

    I am very sorry if im explanin this poorly, english is not my first language
    - Robin

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

      Anonymous,

       

      Hi Robin,

       

      We can add a calculated column to the table. Then we can use this column in the slicer.

      Status =
VAR statuss =
    CALCULATE (
        DISTINCTCOUNT ( [Item Class] ),
        FILTER (
            ALLEXCEPT ( 'Table1', Table1[Item] ),
            'Table1'[Item Class] IN { 1, 2 }
        )
    )
RETURN
    IF ( statuss = 2, 1, 0 )

       

       

       

       

       

       

       

       

       

       

       

       

       

       

      You can check the file here: https://1drv.ms/u/s!ArTqPk2pu-BkgRhSiy4U2C9zaXVG

       

      Best Regards!

      Dale

      • Anonymous's avatar
        Anonymous
        Not applicable
        9 years ago

        Very nice! We are getting closer and closer.

        As shown in the picture, this is as far i have gotten. Youre latest post is nice but i think again it might be a missunderstanding =/ i dose look like you put a great deal of work into it. I apologise for my poor explanation. 

         

        As you can see on the image, there is an overview of items in item class vk2 and what warehouses currently have it in VK2.

        I limited the slicer to only effect that matrix

         

        The other matrix is filtered to only VK1 so when you click an item(vare) it shows what warehouses has that item in vk1.

         

        The only problem now, is that i need a way for a warehouse to figure out if a specifik warehouse has their items in vk1.

        If they have a transport already going from lets say L10 to L60 i want them to be able to see if they can send enny vk2 items over to them if they have it in VK1.

         

        Thank you Dale!

        -Robi