Forum Discussion

minhvuong93's avatar
minhvuong93
Helper II
9 years ago
Solved

[Help] Count with condition across column

Hi all, I have a data of customer annual sales by month and by SalesRep like below. I want to do something like countif , to see: How Many Months did a Customer have the Volume Sales (Volume month ...
Need Help
  • Reid_Havens's avatar
    Reid_Havens
    9 years ago

    Hi there,

     

    Happy to help! So DAX works best calculating rows within a column. It's trickier when you have values for a customer split across columns, however there is a wonderful feature in "Get & Transform (Power Query)" that lets you UnPivot columns onto rows. If you're able to do that in Power Query first then creating the DAX measure is MUCH easier. Your table would look like the one pictured below following the "UnPivoting".

    With the table in this structure you would create two DAX measures: [Total Volume] & [# of Months w/ Sales], with the idea that DAX measures are a lot like lego blocks, one building on the other. The [Total Volume] measure will be referenced in [# of Months w/ Sales] which will count the months of sales.

     

    [Total Volume] Formula:

    = SUM( 'Table'[Volume] )

    [# of Months w/ Sale] Formula:

    =
CALCULATE (
    DISTINCTCOUNT ( 'Table'[Volume Month] ),
    FILTER ( 'Table', [Total Volume] > 0 )
)

    I'm happy to send you over the PBI file with these transformations/formulas as well if you'd like.

     

  • Phil_Seamark's avatar
    Phil_Seamark
    9 years ago

    Have a play with this in the Query Editor

     

    Highlight the three columns you want to "unpivot" then right click and select Unpivot columns

     

    Turned into this....

     

     

Reid_Havens's avatar
Reid_Havens
Most Valuable Professional
9 years ago

Hi there,

 

Happy to help! So DAX works best calculating rows within a column. It's trickier when you have values for a customer split across columns, however there is a wonderful feature in "Get & Transform (Power Query)" that lets you UnPivot columns onto rows. If you're able to do that in Power Query first then creating the DAX measure is MUCH easier. Your table would look like the one pictured below following the "UnPivoting".

With the table in this structure you would create two DAX measures: [Total Volume] & [# of Months w/ Sales], with the idea that DAX measures are a lot like lego blocks, one building on the other. The [Total Volume] measure will be referenced in [# of Months w/ Sales] which will count the months of sales.

 

[Total Volume] Formula:

= SUM( 'Table'[Volume] )

[# of Months w/ Sale] Formula:

=
CALCULATE (
    DISTINCTCOUNT ( 'Table'[Volume Month] ),
    FILTER ( 'Table', [Total Volume] > 0 )
)

I'm happy to send you over the PBI file with these transformations/formulas as well if you'd like.

 

  • minhvuong93's avatar
    minhvuong93
    Helper II
    9 years ago

    Reid_Havens

    Thanks Reid,

    Actually I have more than 36 month column and million of rows. But I will have a try since this is better than create a column function.. ,

    Could you please kindly send me the file you mentioned as well :)

    Highly appreciate your quick support,

    Cheers,

     

    • Phil_Seamark's avatar
      Phil_Seamark
      Microsoft Employee
      9 years ago

      Have a play with this in the Query Editor

       

      Highlight the three columns you want to "unpivot" then right click and select Unpivot columns

       

      Turned into this....

       

       

  • minhvuong93's avatar
    minhvuong93
    Helper II
    9 years ago

    Hi Reid_Havens

    I tried to use your formula but  it was taking forever to calculate the result.

    Is there any faster way to perform this :(

    • Reid_Havens's avatar
      Reid_Havens
      Most Valuable Professional
      9 years ago

      Hi minhvuong93,

       

      Three main factors determine how fast something calculate: The size of the data, speed of the machine the calculation is being ran on, and the complexity of the formula. Could you answer the first two for me? The SUM and DISTINCTCOUNT formulas in DAX are very basic calculations that should require very little processing power to calculate.