Need help to build DAX Expression

[Anonymous]

Hi all,

I have data like below table, by using this table I want to show the percentage in Card  

Step 1:

any record has blank I want to skip that record (Dax expression level) and calculate the number of shipping & number of Load Type based on Concatenation Field

Step 2 :

= ((sum(shipping id) where is # distinct Shipping ID > 1 and  #distinct Load Type =1)   /  sum(overall shipment)) *100

= (7/ 17) *100

= 41% (Need to show in Card)

Step 3 :

= ((sum(shipping id) where is # distinct Shipping ID > 1 and  #distinct Load Type >1)   /  sum(overall shipment)) *100

=(5/17) *100

=29.4% (Need to show in Card)

Data:

Shipper	From	To	Product	load type
S1	LA	TA	P1	FL
S1	LA	TA	P1	FL
S1	LA	TA	P1	FL
S1	LA	TA	P1	FL
S1	LB	TB	P2	FL
S1	LB	TB	P2	PL
S1	LB	TB	P2	PL
S2	LB	TB	P2	FL
S2	LB	TB	P3	FL
S2	Blank	Blank	P3	PL
S2	LB	Blank	Blank	FL
S2	LB	Blank	P3	Blank
S3	LA	TA	P1	FL
S3	LA	TA	P1	FL
S3	LA	TA	P1	FL
S1	LC	TC	P2	PL
S1	LC	TC	P2	FL

[Zubair_Muhammad · Accepted solution]

@Anonymous 

Try this MEASURE for step 2 result.

It works with sample data

Measure =
VAR ExBlanks =
    FILTER (
        Table1,
        [Shipper] <> BLANK ()
            && [From] <> BLANK ()
            && [To] <> BLANK ()
            && [Product] <> BLANK ()
    )
RETURN
    SUMX (
        FILTER (
            SUMMARIZE (
                ExBlanks,
                [Shipper],
                [From],
                [To],
                [Product],
                "ID", DISTINCTCOUNT ( Table1[Shipping ID] ),
                "Load", DISTINCTCOUNT ( Table1[load type] )
            ),
            [ID] > 1
                && [Load] = 1
        ),
        [ID]
    )
        / COUNTROWS ( Table1 )

[Zubair_Muhammad · Accepted solution]

@Anonymous 

For step 3

Measure 2 = 
VAR ExBlanks =
    FILTER (
        Table1,
        [Shipper] <> BLANK ()
            && [From] <> BLANK ()
            && [To] <> BLANK ()
            && [Product] <> BLANK ()
    )
RETURN
    SUMX (
        FILTER (
            SUMMARIZE (
                ExBlanks,
                [Shipper],
                [From],
                [To],
                [Product],
                "ID", DISTINCTCOUNT ( Table1[Shipping ID] ),
                "Load", DISTINCTCOUNT ( Table1[load type] )
            ),
            [ID] > 1
                && [Load] > 1
        ),
        [ID]
    )
        / COUNTROWS ( Table1 )

[Zubair_Muhammad · Accepted solution]

@Anonymous 

Try this MEASURE for step 2 result.

It works with sample data

Measure =
VAR ExBlanks =
    FILTER (
        Table1,
        [Shipper] <> BLANK ()
            && [From] <> BLANK ()
            && [To] <> BLANK ()
            && [Product] <> BLANK ()
    )
RETURN
    SUMX (
        FILTER (
            SUMMARIZE (
                ExBlanks,
                [Shipper],
                [From],
                [To],
                [Product],
                "ID", DISTINCTCOUNT ( Table1[Shipping ID] ),
                "Load", DISTINCTCOUNT ( Table1[load type] )
            ),
            [ID] > 1
                && [Load] = 1
        ),
        [ID]
    )
        / COUNTROWS ( Table1 )

[Anonymous]

Thanks  Zubair_Muhammad