Forum Discussion

mmcgwire's avatar
mmcgwire
Frequent Visitor
8 years ago
Solved

Display minimum value on every row under group

Hi everyone:

 

I have been struggling with this for a couple days.  

 

I need to be able to display (and then reference) the minimum value of the % of calculation, which is in a measure. This minimum value needs to be displayed in every row for the giving SKU. Here is a picture as an example:

I would like the additional column to display 5.0% for every instance of this SKU.  If there are multiple SKUs present then I would like it to display the minimum for that specific SKU.

 

I cannot get this value to display with MINX().  I also tried to get the minimum units (eg. 6) to display then calculate based off that, but even that is not correct (it is returning the minimums units at the line item level).

 

The end users do not want to filter by style, but rather pull large exports with the information automatically calculating based on SKU and the minimum SizeBreak% required.

 

Let me know if any other information is needed.

 

Thanks.

Need Help
  • mmcgwire's avatar
    mmcgwire
    8 years ago

    Since making this calculation off the measures I already have doesn't work, I decided to create a new table in my DB then create calculated columns to get the answers I needed.  Not ideal, but it works.

     

    Thanks everyone for your input.

7 Replies

    • mmcgwire's avatar
      mmcgwire
      Frequent Visitor
      8 years ago

      Thanks for the reponse.

       

      I did try using SUMMARIZE to create a new table then calculate based off that, which did not work.  Below is the code and result.  Let me know if you see anything I missed.  Thanks.

       

      MINX(
    SUMMARIZE(SALES_OrderSize, 
        PROD__Master_StyleColor[_sc DESC], 
        INVEN_Stock[Size], 
        "tmp", 
        DIVIDE(
            SUM(SALES_OrderSize[NetOrdUnits]),
                CALCULATE(
                    SUM(SALES_OrderSize[NetOrdUnits]),
                        ALLEXCEPT(SALES_OrderSize, 
                            PROD__Master_StyleColor[_sc DESC], 
                            INVEN_Warehouse[WHSE_CD], 
                            PROD_Attributes[Active Sizes], 
                            DATE_OrderEntry[Year], 
                            CUST__Master[Cust Channel], 
                            SALES_OrderHeader[OR_CODE],
                            CUST__Master[Customer],
                            PROD__Master_StyleColor[_key_sc]
                        )
                )
                ,0
            )
    ), [tmp]
)

    • mmcgwire's avatar
      mmcgwire
      Frequent Visitor
      8 years ago

      anandav

      I should have mentioned that these are all measures.  The table is too large to handle calculated columns (approx. 4M rows).

       

      Also the SKU is not listed on the current table and will not populate under the EARLIER() functions (I don't actually know it won't populate). Otherwise this would probably work.

      • anandav's avatar
        anandav
        Skilled Sharer
        8 years ago

        I'm not sure how to do this if these are all measures.

         

        1] Assuming you can create a dynamic table with the fields SKU, Size, etc.

        I have created a normal data table from data file for demo.

        2] I created a measure on this table for the NewSize%

        3] Dynamically create another table using the table in step#1 and added a new column for Minimum Size%

         

        I understand still the 4M rows performance impact will apply, but putting this forward as a thought.

         

         

        Hope this helps.