Solution on Adding Text Box in Power BI with Dynamic Messages

Problem Analysis

You want to dynamically display a text message in Power BI that identifies the machine with the highest rejection rate and the corresponding rejection quantity. Your dataset has a calculation for quality percentage using the formula Quality % = DIVIDE(SUM('Data'[Produce Qty]) - SUM('Data'[Rej]), SUM('Data'[Produce Qty])).

Step-by-Step Solution

1. Calculate Highest Rejection Machine and Quantity

First, we need to identify the machine with the highest rejection quantity. This involves calculating the rejection quantity for each machine and then identifying the machine with the highest value.

DAX Measures

Highest Rejection Machine = 
VAR MaxRej = 
    MAXX(
        SUMMARIZE(
            'Data', 
            'Data'[Machine No], 
            "Rejections", SUM('Data'[Rej])
        ),
        [Rejections]
    )
RETURN
    SELECTCOLUMNS(
        FILTER(
            SUMMARIZE(
                'Data', 
                'Data'[Machine No], 
                "RejectionQty", SUM('Data'[Rej])
            ), 
            [RejectionQty] = MaxRej
        ), 
        "Machine", 'Data'[Machine No]
    )

Highest Rejection Qty = 
MAXX(
    SUMMARIZE(
        'Data',
        'Data'[Machine No],
        "RejQty", SUM('Data'[Rej])
    ),
    [RejQty]
)

2. Create a Concatenated Text Measure

Next, create a new measure that will concatenate the machine number and rejection quantity into a single text string.

Rejection Message = 
"Highest Rejection done by " & 
MAXX(Highest Rejection Machine, [Machine]) & 
" produced highest rejection of " & 
[Highest Rejection Qty] & 
" Qty."

3. Add a Text Box in Power BI

• Go to the "Insert" tab in Power BI.
• Click on "Text Box" to add a new text box to your report.
• In the text box, click on the data field button (fx) in the toolbar to bind the text box to the Rejection Message measure.

4. Customize the Text Box

You should ensure the text box is configured to display the dynamic content appropriately:

• Adjust font size, color, and style as needed.
• Resize or reposition the text box within the report canvas to ensure it is clearly visible.

Complete Example

Highest Rejection Machine = 
VAR MaxRej = 
    MAXX(
        SUMMARIZE(
            'Data', 
            'Data'[Machine No], 
            "Rejections", SUM('Data'[Rej])
        ),
        [Rejections]
    )
RETURN
    SELECTCOLUMNS(
        FILTER(
            SUMMARIZE(
                'Data', 
                'Data'[Machine No], 
                "RejectionQty", SUM('Data'[Rej])
            ), 
            [RejectionQty] = MaxRej
        ), 
        "Machine", 'Data'[Machine No]
    )

Highest Rejection Qty = 
MAXX(
    SUMMARIZE(
        'Data',
        'Data'[Machine No],
        "RejQty", SUM('Data'[Rej])
    ),
    [RejQty]
)

Rejection Message = 
"Highest Rejection done by " & 
MAXX(Highest Rejection Machine, [Machine]) & 
" produced highest rejection of " & 
[Highest Rejection Qty] & 
" Qty."

Final Considerations

Ensure you validate the measures and the text box binding to confirm that they are working as expected. Adjust any styling options within Power BI to align with your report's visual standards.

---

This approach leverages DAX for complex calculations and text concatenation, producing a dynamic and informative text message in the Power BI report text box.