Need help with Measure - Points Calculation

[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
831257		5
831259	3
831261		5
831264	3
831265	3
831265		5

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

[amitchandak]

@gauravnarchal , You can create a new column in Category and use that

if([Category] = "Hand Made",3,5)

This can be used in measure or displayed as per need

[gauravnarchal]

@amitchandak - I want this a measure and with below condition

• 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

[v-kkf-msft · Accepted solution]

Hi @gauravnarchal ，

Please try the following measures:

Result = 
var _category = 
    CALCULATE ( 
        MAX ( Category[Category] ),
        FILTER ( 
            InvoiceDetails,
            InvoiceDetails[Item] <> BLANK()
        )
    )
return 
    SWITCH(
        _category, 
        "Hand Made", 3,
        "Machine", 5,
        BLANK()
    )

Total_Result = 
IF (
    ISFILTERED ( InvoiceNumber[InvoiceDetailID] ),
    [Result],
    SUMX (
        FILTER ( InvoiceDetails, InvoiceDetails[Item] <> BLANK() ),
        [Result]
    )
)

   

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.