DAX Calculation including 2 columns and slicer

[nitinbalodi]

Hello all, I am trying to write a DAX measure for my report where I have a table as shown below and need to show the Category and Subcategory which will be connected to my slicer. I have mentioned calculation and output required. Let me know, if more clarity is required. Thanks.

Input table

Ticket#	Category	SubCategory	SLA
Ticket 1	A	A-1	Yes
Ticket 2	B	B-1	Yes
Ticket 3	B	B-2	No
Ticket 4	B	B-2	No
Ticket 5	A	A-1	No
Ticket 6	A	A-2	No
Ticket 7	B	B-2	No
Ticket 8	B	B-3	Yes
Ticket 9	B	B-3	Yes
Ticket 10	B	B-1	No

calculation:

Category	SubCategory	Total Tickets	SLA = YES	SLA%
A	A-1	2	1	50%
A	A-2	1	0	0%
B	B-1	2	1	50%
B	B-2	3	0	0%
B	B-3	2	2	100%

Output:

If Slicer selection is A then card visual should show

                         A -> A-1 50%

If Slicer selection is B then card visual should show

                      B -> B-3 100%

[tamerj1 · Accepted solution]

Hi @nitinbalodi 
Here is a sample file with the solution https://www.dropbox.com/t/xUJUyAWUTD6YmKrI

Measures are

Total Tickets = COUNTROWS ( Input )

SLA (YES) = CALCULATE ( COUNTROWS ( Input ), Input[SLA] = "Yes" )

SLA% = DIVIDE ( [SLA (YES)], [Total Tickets] )

CARD = 
VAR SummaryTable = 
    SUMMARIZE ( Input, Input[Category], Input[SubCategory] )
VAR MaximumSLAPercentage =
    MAXX ( 
        SummaryTable,
        [SLA%]
    )
VAR Result =
CONCATENATEX ( 
    FILTER ( 
        SummaryTable,
        [SLA%] = MaximumSLAPercentage
    ),
    Input[SubCategory] & " (" & FORMAT ( [SLA%], "#,#0.0%" ) & ")"
)
RETURN
    Result

[nitinbalodi]

Thank you @tamerj1 , this is what I was looking for...

[Jihwan_Kim]

Hi,

I am not sure if I understood your question correctly, but please check the below picture and the attached pbix file.

Total tickets: = 
COUNTROWS( Data )

SLA Yes: = 
CALCULATE ( [Total tickets:], Data[SLA] = "Yes" )

SLA Yes Ratio: =
IF (
    HASONEVALUE ( Data[SubCategory] ),
    DIVIDE ( [SLA Yes:], [Total tickets:] ),
    MAXX (
        ADDCOLUMNS (
            ALL ( Data[SubCategory] ),
            "@SLAYes", CALCULATE ( DIVIDE ( [SLA Yes:], [Total tickets:] ) )
        ),
        Data[SubCategory]
    ) & " "
        & FORMAT (
            MAXX (
                ADDCOLUMNS (
                    ALL ( Data[SubCategory] ),
                    "@SLAYes", CALCULATE ( DIVIDE ( [SLA Yes:], [Total tickets:] ) )
                ),
                [@SLAYes]
            ),
            "#,#0.0%"
        )
)

[nitinbalodi]

Thanks for the response, this works fine, only one issue, when I select Category "A" in slicer it is not showing "A1" as selected value instead it is showing B-3 for any selection.