Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
Solved! Go to 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
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 )
@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 )
@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 )
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
97 | |
84 | |
67 | |
60 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |