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.
Hello Community,
I have one Taste Rating dataset which contains following column.
I want to compare one primary taster rating with other taster. and i want to slicer for taster name. One is for primary which is in single select and other is secondary taster.
UniqueID | TasterName | TastingScore |
100 | X | 20 |
100 | Y | 22 |
100 | Z | 20 |
200 | X | 23 |
200 | Y | 23 |
300 | X | 24 |
300 | Z | 26 |
400 | Y | 30 |
400 | Z | 31 |
Required Output
Primary Taster = x (Suppose)
Then
Taster | Number of Lot Tasted | Match Lot | % Match |
Y | 2 | 1 | 50% |
z | 2 | 1 | 50% |
Example:
Number of Lot Tasted = distinct count of uniqueid which both x and y tasted
match Lot : out of number of lot tasted which lot rating match
Solved! Go to Solution.
Hi @Dhrutivyasa-070
Please refer to attached sample file with the proposed solution.
Number of Lot Tasted =
VAR PrimaryTaster = SELECTEDVALUE ( Tasters[Primary Taster] )
VAR Taster = SELECTEDVALUE ( 'Table'[TasterName], PrimaryTaster )
RETURN
IF (
Taster <> PrimaryTaster,
SUMX (
VALUES ( 'Table'[UniqueID] ),
INT (
PrimaryTaster
IN CALCULATETABLE (
VALUES ( 'Table'[TasterName] ),
ALL ( 'Table'[TasterName] )
)
)
)
)
Match Lot =
VAR PrimaryTaster = SELECTEDVALUE ( Tasters[Primary Taster] )
VAR Taster = SELECTEDVALUE ( 'Table'[TasterName], PrimaryTaster )
RETURN
IF (
Taster <> PrimaryTaster,
SUMX (
SUMMARIZE ( 'Table', 'Table'[UniqueID], 'Table'[TastingScore] ),
INT (
PrimaryTaster
IN CALCULATETABLE (
VALUES ( 'Table'[TasterName] ),
ALL ( 'Table'[TasterName] )
)
)
)
)
% Match = DIVIDE ( [Match Lot], [Number of Lot Tasted] )
Hi @Dhrutivyasa-070
Please refer to attached sample file with the proposed solution.
Number of Lot Tasted =
VAR PrimaryTaster = SELECTEDVALUE ( Tasters[Primary Taster] )
VAR Taster = SELECTEDVALUE ( 'Table'[TasterName], PrimaryTaster )
RETURN
IF (
Taster <> PrimaryTaster,
SUMX (
VALUES ( 'Table'[UniqueID] ),
INT (
PrimaryTaster
IN CALCULATETABLE (
VALUES ( 'Table'[TasterName] ),
ALL ( 'Table'[TasterName] )
)
)
)
)
Match Lot =
VAR PrimaryTaster = SELECTEDVALUE ( Tasters[Primary Taster] )
VAR Taster = SELECTEDVALUE ( 'Table'[TasterName], PrimaryTaster )
RETURN
IF (
Taster <> PrimaryTaster,
SUMX (
SUMMARIZE ( 'Table', 'Table'[UniqueID], 'Table'[TastingScore] ),
INT (
PrimaryTaster
IN CALCULATETABLE (
VALUES ( 'Table'[TasterName] ),
ALL ( 'Table'[TasterName] )
)
)
)
)
% Match = DIVIDE ( [Match Lot], [Number of Lot Tasted] )
Hello tamerj1,
i have facing one more problem.
in my data set another column add which is rating category. Rating score are categories for increasing match percetage.
UniqueID | Taster | Score | Category |
100 | x | 20 | a |
100 | y | 22 | b |
100 | z | 20 | a |
200 | x | 23 | b |
200 | y | 23 | b |
300 | x | 24 | a |
300 | z | 26 | b |
400 | x | 30 | a |
400 | z | 31 | a |
My Primary Taster is X and Secondary Taster is Z nd Catogory A selected in Slicer.
Required Output :
Number of Lot Tasted : 3 (Number of Lot both tasted nd primary taster category =a)
Match : 2.
All Comparision based on Primary Taster.
@Dhrutivyasa-070
I don't fully understand the requiremnt.
Shall the number of lot tasted ignore the category?
How did you calculate match = 2?
Thank You Very Much @tamrej1
Its working Properly in my Dataset.
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 |
---|---|
40 | |
26 | |
20 | |
14 | |
8 |
User | Count |
---|---|
76 | |
48 | |
46 | |
20 | |
16 |