Display minimum value on every row under group

[mmcgwire]

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.

[Ashish_Mathur]

Hi,

Try this

=CALCULATE(MIN([SizeBreak % (_sc DESC)]),ALL(Data[Size]))

[anandav]

@mmcgwire,

Will the below DAx work for you?

(Add as new column)

[mmcgwire]

@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]

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.

[mmcgwire · Accepted solution]

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.

[Greg_Deckler]

You probably need to create a new measure and use VAR along with SUMMARIZE or ADDCOLUMN to create a temp table that is essentially the table you show below but as a VAR. Then you can use MINX to grab the minimum of that and return it. You'll probably need an ALL statement in there to remove row context.

See my article here: https://community.powerbi.com/t5/Community-Blog/Design-Pattern-Groups-and-Super-Groups/ba-p/138149

[mmcgwire]

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]
)