Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

resell report

I am trying to build a report showing all items sold on orders with selected products.  I have two tables.  One has orders and th4e other has order details.  I want to be able to select a single product from the details talbe then show all orders that had this item on them.  I can show totals from the orders table but I also want to show the details ffrom the detaikls table but I don't want to limit the details to the select items.  this is what I am talkign about.

Orders   
OrderID   Sales   Tax

1              5.0       0.5

2              25.0     2.5

3              10.0     1.0

OrderDetails
OrderID    ItemID   Sales
1               a            2

1               b            3

2               a            2

2               c            23

3               b            3
3               c             7

If these are the two tables and I selet Item b

I want to return this data
OrderID   Item   Sales

1              a         2

1              b         3

3              b         3

3              c          7

 

The b4est way to describe this is a cosell report telling me what items are sold with a selected item.  I can get the summary data from the roders table but I am unsure how to go back and get the details from those orders without havign it filtered by the selection.

 

Need Help
  • AlB's avatar
    AlB
    7 years ago

    Hi Anonymous

     

    Try the following:

     

    1. Create a new one-column table, FilterTable, with all values of OrderDetails[ItemID] in FilterTable[ItemID]. Set FilterTable[ItemID] in a slicer. This will be used to select the the item of interest.

    2. Place a table visual in your report and set in values OrderDetails[ItemID], OrderDetails[OrderID] and OrderDetails[Sales]. Choose "don't summarize" for all three. 

    3. Create this measure that will determine what items are to be shown:

     

    ShowMeasure = 
VAR _OrdersList =
    CALCULATETABLE (
        DISTINCT ( OrderDetails[OrderID] );
        OrderDetails[ItemID] IN DISTINCT ( FilterTable[ItemID] );
        ALL ( OrderDetails )
    )
VAR _CurrentOrder =
    SELECTEDVALUE ( OrderDetails[OrderID] )
RETURN
    IF ( _CurrentOrder IN _OrdersList; 1 )

     

    4. Place [ShowMesure] in the visual level filters of the table visual and select "Show values when the value is:" --> 1

     

     

    Code formatted with  

3 Replies

  • AlB's avatar
    AlB
    Icon for Community Champion rankCommunity Champion
    7 years ago

    Hi Anonymous

     

    Try the following:

     

    1. Create a new one-column table, FilterTable, with all values of OrderDetails[ItemID] in FilterTable[ItemID]. Set FilterTable[ItemID] in a slicer. This will be used to select the the item of interest.

    2. Place a table visual in your report and set in values OrderDetails[ItemID], OrderDetails[OrderID] and OrderDetails[Sales]. Choose "don't summarize" for all three. 

    3. Create this measure that will determine what items are to be shown:

     

    ShowMeasure = 
VAR _OrdersList =
    CALCULATETABLE (
        DISTINCT ( OrderDetails[OrderID] );
        OrderDetails[ItemID] IN DISTINCT ( FilterTable[ItemID] );
        ALL ( OrderDetails )
    )
VAR _CurrentOrder =
    SELECTEDVALUE ( OrderDetails[OrderID] )
RETURN
    IF ( _CurrentOrder IN _OrdersList; 1 )

     

    4. Place [ShowMesure] in the visual level filters of the table visual and select "Show values when the value is:" --> 1

     

     

    Code formatted with  

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Thanks this works

  • Vvelarde's avatar
    Vvelarde
    Icon for Community Champion rankCommunity Champion
    7 years ago

    Anonymous

     

    Hi, try with this

     

    1. Create a new table

     

    ItemsID=Values(OrdersDetail[ItemsID])

     

    2. Create a measure:

     

    FilterM =
VAR _OrderID =
    SELECTEDVALUE ( OrderDetails[OrderID] )
RETURN
    IF (
        COUNTROWS (
            FILTER (
                ALL ( OrderDetails ),
                OrderDetails[ItemID] IN VALUES ( ItemID[ItemID] )
                    && OrderDetails[OrderID] = _OrderID
            )
        )
            > 0,
        1,
        BLANK ()
    )

    3. Put the measure in the Visual level filter and set to 1

     

    Regards

     

    Victor