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

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

  • 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