Forum Discussion

gauravnarchal's avatar
gauravnarchal
Post Prodigy
4 years ago
Solved

Need help with Measure - Points Calculation

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 

 

InvoiceNumberHand MadeMachine
831257 5
8312593 
831261 5
8312643 
8312653 
831265 5

 

 

 

Table:- Category

 

InvoiceIDInvoiceDetailIDCategory
9424972534258Machine
9424982534260Hand Made
9424992534268Machine
9425002534270Machine
9425012534272Hand Made
9425022534274Hand Made
9425022539274Machine

 

Table:- InvoiceNumber

 

InvoiceIDInvoiceDetailIDInvoiceDateInvoiceNumber
942497253425811/1/2021 0:00831257
942498253426011/1/2021 0:00831259
942499253426811/1/2021 0:00831260
942500253427011/1/2021 0:00831261
942501253427211/1/2021 0:00831264
942502253427411/1/2021 0:00831265
942502253927411/1/2021 0:00831265

 

Table:- InvoiceDetails

 

InvoiceIDInvoiceDetailIDItem
9424972534258UBY4SL
9424982534260URH/NOV/051121
9424992534268 
94250025342709876865790
94250125342729876865790/91
94250225342749876865789
94250225392749876865689
Need Help
Show and Tell
  • v-kkf-msft's avatar
    v-kkf-msft
    4 years ago

    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.

3 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    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's avatar
      gauravnarchal
      Post Prodigy
      4 years ago

      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 

       

      InvoiceNumberHand MadeMachineTotal
      831257 55
      8312593 3
      831261 55
      8312643 3
      8312653 3
      831265 55
      Grand Total91524

       

      • v-kkf-msft's avatar
        v-kkf-msft
        Community Support
        4 years ago

        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.