Points Calculation using Measure

[gauravnarchal]

Hello

I need your help to create a measure as

• Count “item” from table InvoiceDetails and multiply each item with the points below and show the result in matrix visual.
• If Hand Made Multiply by 3
• If Machine Multiply by 5

Result - Matrix Visual 

InvoiceNumber	Hand Made	Machine	Total
831257		5	5
831259	3		3
831261		5	5
831264	3		3
831265	3		3
831265		5	5
Grand Total	9	15	24

Table:- Category

InvoiceID	InvoiceDetailID	Category
942497	2534258	Machine
942498	2534260	Hand Made
942499	2534268	Machine
942500	2534270	Machine
942501	2534272	Hand Made
942502	2534274	Hand Made
942502	2539274	Machine

Table:- InvoiceNumber

InvoiceID	InvoiceDetailID	InvoiceDate	InvoiceNumber
942497	2534258	11/1/2021 0:00	831257
942498	2534260	11/1/2021 0:00	831259
942499	2534268	11/1/2021 0:00	831260
942500	2534270	11/1/2021 0:00	831261
942501	2534272	11/1/2021 0:00	831264
942502	2534274	11/1/2021 0:00	831265
942502	2539274	11/1/2021 0:00	831265

Table:- InvoiceDetails

InvoiceID	InvoiceDetailID	Item
942497	2534258	UBY4SL
942498	2534260	URH/NOV/051121
942499	2534268
942500	2534270	9876865790
942501	2534272	9876865790/91
942502	2534274	9876865789
942502	2539274	9876865689

[DataInsights · Accepted solution]

@gauravnarchal,

Try this solution.

Data model:

The relationships defaulted to one-to-one based on your example data.

Measures:

Item Count = COUNT ( InvoiceDetails[Item] )

Category Count = 
SUMX (
    InvoiceNumber,
    VAR vCategory =
        RELATED ( Category[Category] )
    VAR vFactor =
        IF ( vCategory = "Hand Made", 3, 5 )
    RETURN
        [Item Count] * vFactor
)

Be sure that InvoiceDetails[Item] contains BLANK (null) and not empty strings.

Result: