Forum Discussion

GomzeeR's avatar
GomzeeR
Regular Visitor
6 years ago
Solved

Help with Custome Column

Hi all,

 

I am still new to Power BI and I have a problem that I am unable to solve.

 

I have records with Order numbers with different charge Type Code.

 

I could manage to capture all the order numbers with Charge Type Code "1".

 

But I also want to capture the Order numbers that does not have Charge Type Code "1".

 

What ever Logic I use, I am unable to achieve the Count for the Order Numbers without the Charge Type Code "1".

 

 
 

 

 

 

 

I have used the below formula to get the Order numbers for Charge Type Code "1".

 

If(Table3[Charge Type Code] = "1", 1,0)

 

Appreciate any help with this.

 

 

Kind regards,

 

Gowtham

Need Help
  • amitchandak's avatar
    amitchandak
    6 years ago

    Try like

    Charge type order 1 count =
sumx(summarize(table3, table3[Order_no],"_ch1" ,sumx(table3,If(Table3[Charge Type Code] = "1", 1,0)),"_all",count(Order_no))
		if(_ch1=_all,1,0))
		
Charge type order not 1 count =
sumx(summarize(table3, table3[Order_no],"_ch1" ,sumx(table3,If(Table3[Charge Type Code] = "1", 1,0)),"_all",count(Order_no))
		if(_ch1=_all,0,1))

     

    Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
    Thanks. My Recent Blog -
    Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
    Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges

    Connect on Linkedin

  • dax's avatar
    dax
    6 years ago

    Hi GomzeeR , 

    I am not clear about your requirement, if you want to get distinct count of order number, I think you could try below measures

    Measure = CALCULATE(DISTINCTCOUNT('Table'[Order No.]), 'Table'[Charge Type Code]="1")
Measure2 = CALCULATE(DISTINCTCOUNT('Table'[Order No.]), 'Table'[Charge Type Code]<>"1")


    If not, could you please inform me more detailed information(such as your expected output and your sample data)? Then I will help you more correctly.

    Please do mask sensitive data before uploading.

    Thanks for your understanding and support.
    Best Regards,
    Zoe Zhi

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

5 Replies

  • vivran22's avatar
    vivran22
    Community Champion
    6 years ago

    Hi Gowtham,

     

    Please try following measures:

     

    Count Charge type 1  = COUNTAX(table3, IF(Table3[Charge Type Code] = "1", [Charge Type Code]))

     

     

    Count Charge type not 1  = COUNTAX(table3, IF(Table3[Charge Type Code] <> "1", [Charge Type Code]))

     

     

    Regards,

    Vivek

    • GomzeeR's avatar
      GomzeeR
      Regular Visitor
      6 years ago

      Hi Vivek,

       

      Thank you for the reply.

       

      This formula is not working. It counts the Order number multiple times as there are multiple Charge Codes for one order number.

       

      Ex: One Customer has 219 Unique order numbers, and there are 50 orders Charged, so I need to get 169 as the value for the orders that didnt have a "1" Charge.

       

      It is very tricky.

       

      Kind regards,

       

      gowtham

      • vivran22's avatar
        vivran22
        Community Champion
        6 years ago
        May I request you to share the sample data file with the required output as an example.
  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    Try like

    Charge type order 1 count =
sumx(summarize(table3, table3[Order_no],"_ch1" ,sumx(table3,If(Table3[Charge Type Code] = "1", 1,0)),"_all",count(Order_no))
		if(_ch1=_all,1,0))
		
Charge type order not 1 count =
sumx(summarize(table3, table3[Order_no],"_ch1" ,sumx(table3,If(Table3[Charge Type Code] = "1", 1,0)),"_all",count(Order_no))
		if(_ch1=_all,0,1))

     

    Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
    Thanks. My Recent Blog -
    Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
    Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges

    Connect on Linkedin

  • dax's avatar
    dax
    Community Support
    6 years ago

    Hi GomzeeR , 

    I am not clear about your requirement, if you want to get distinct count of order number, I think you could try below measures

    Measure = CALCULATE(DISTINCTCOUNT('Table'[Order No.]), 'Table'[Charge Type Code]="1")
Measure2 = CALCULATE(DISTINCTCOUNT('Table'[Order No.]), 'Table'[Charge Type Code]<>"1")


    If not, could you please inform me more detailed information(such as your expected output and your sample data)? Then I will help you more correctly.

    Please do mask sensitive data before uploading.

    Thanks for your understanding and support.
    Best Regards,
    Zoe Zhi

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