cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
minhvuong93 Regular Visitor
Regular Visitor

[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 >0). This should be the total volume of the customer (does not matter if 2 or more Sales Rep sold to him)

 

For Excel it is easy to use Countif like below. But I don't know how to achieve the same result in DAX ?

 

powerbi.PNG

 Thanks for your kind help,

 

 

2 ACCEPTED SOLUTIONS

Accepted Solutions
Reid_Havens Member
Member

Re: [Help] Count with condition across column

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".

UnPivoted.png

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.

 

Super User
Super User

Re: [Help] Count with condition across column

Have a play with this in the Query Editor

 

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

 

unpivot1.png

Turned into this....

 

unpivot 2.png

 


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

8 REPLIES 8
Super User
Super User

Re: [Help] Count with condition across column

Hi @minhvuong93

 

In the short term, you could add the following calculated column to your table to simulate the COUNTIF function

 

 

Number of Times Buying = 
              IF([Volume Jan]>0,1,0) 
            + IF([Volume Feb]>0,1,0) 
            + IF([Volume Mar]>0,1,0) 

 

However,  I recommend you pivot your data structure to be the following which will make it alot easier to perform a variety of calculations and not rely on hardcoding as per my above column

 

 

SalesRep , CustomerID , Month  , Volume
----------------------------------------
SR1 , C1 , Jan 17 , 1
SR1 , C1 , Mar 17 , 1
SR2 , C1 , Mar 17 , 1
etc ....

 


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

minhvuong93 Regular Visitor
Regular Visitor

Re: [Help] Count with condition across column

Thanks @Phil_Seamark ,

The problem is the data provided for me is fixed like that. 

How can I restructure the data from what I have into like you mention above?

Since it is a very large data with million of rows

 

Reid_Havens Member
Member

Re: [Help] Count with condition across column

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".

UnPivoted.png

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.

 

Super User
Super User

Re: [Help] Count with condition across column

Hi @minhvuong93

 

You can pivot it either in the Query Editor or in DAX.  If you can do it in the Query Editor then your model will have less work to do.

 

 


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

minhvuong93 Regular Visitor
Regular Visitor

Re: [Help] Count with condition across column

@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 Smiley Happy

Highly appreciate your quick support,

Cheers,

 

Super User
Super User

Re: [Help] Count with condition across column

Have a play with this in the Query Editor

 

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

 

unpivot1.png

Turned into this....

 

unpivot 2.png

 


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

minhvuong93 Regular Visitor
Regular Visitor

Re: [Help] Count with condition across column

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 Smiley Sad

Highlighted
Reid_Havens Member
Member

Re: [Help] Count with condition across column

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.