Forum Discussion

JJiso20's avatar
JJiso20
Frequent Visitor
3 years ago
Solved

Replicating COUNTIFS Excel formulars in PowerBI

Hi,

I have an excel file with basic countifs excel formulars that i'm trying to build in Power BI and struggling with writing the excel formula in DAX.

 

I want to replicate the summarised table and transposed table (shown below) in Power BI. An example of the Raw data is also below.

 

Explaining the formular's:

[Spend>=$200]  = counting the number of clients that spent $200 or more during the previous month and current mont

[Spend<$80]  = counting the number of clients that spent $200 or more 2 months back and previous month but spent

[%Risk]  = Divide the current months [Spend<$80] by the previous months [Spend>=$200]

 

September Example:

The summarised table excel forumlars:

[Spend>=$200]   =COUNTIFS(Table2[Aug-22],">=200",Table2[Sep-22],">=200")   which gives us 3

[Spend<$80]  =COUNTIFS(Table2[Jul-22],">=200",Table2[Aug-22],">=200",Table2[Sep-22],"<80")    which gives us 1

[%Risk]  =Sept-22 [Spend<$80] / Aug-22 [Spend>=$200]    which gives us 1/3 = 33%

 

Summarised table using formulars on transposed table

 Apr-22May-22Jun-22Jul-22Aug-22Sep-22Oct-22
Spend>=$2000432331
Spend <$800021012
Risk0%0%50%33%0%33%67%

 

The transposed table based on Raw data (Table 2)

NameApr-22May-22Jun-22Jul-22Aug-22Sep-22Oct-22
Client A$1,000$1,000$1,000$1,000$1,000$1,000$1,000
Client B$100$200$300$400$80$60$0
Client C$200$200$70$200$200$60$60
Client D$300$300$300$70$300$60$60
Client E$400$70$70$400$400$400$60
Client F$500$500$70$70$500$500$70

 

Raw Data looks like this

Client NameOrder MonthSpend$
Client AApr-22$1,000
Client BApr-22$100
Client CApr-22$200
Client DApr-22$300
Client EApr-22$400
Client FApr-22$500
Client AAug-22$1,000
Client BAug-22$80
Client CAug-22$200
Client DAug-22$300
Client EAug-22$400
Client FAug-22$500
Client AJul-22$1,000
Client BJul-22$400
Client CJul-22$200
Client DJul-22$70
Client EJul-22$400
Client FJul-22$70
Client AJun-22$1,000
Client BJun-22$300
Client CJun-22$70
Client DJun-22$300
Client EJun-22$70
Client FJun-22$70
Client AMay-22$1,000
Client BMay-22$200
Client CMay-22$200
Client DMay-22$300
Client EMay-22$70
Client FMay-22$500
Client AOct-22$1,000
Client BOct-22$0
Client COct-22$60
Client DOct-22$60
Client EOct-22$60
Client FOct-22$70
Client ASep-22$1,000
Client BSep-22$60
Client CSep-22$60
Client DSep-22$60
Client ESep-22$400
Client FSep-22$500

 

Any help would be appreciated

 

Thanks

Joe

Need Help
  • DataInsights's avatar
    DataInsights
    3 years ago

    JJiso20,

     

    Try these measures:

     

    Spend = SUM ( ClientSpend[Spend$] )
    Spend over $200 = 
VAR vTableBase =
    ADDCOLUMNS (
        VALUES ( ClientSpend[Client Name] ),
        "@AmountCurrent", [Spend],
        "@AmountLastMonth", CALCULATE ( [Spend], DATEADD ( DimDate[Date], -1, MONTH ) )
    )
VAR vTableFilter =
    FILTER ( vTableBase, [@AmountCurrent] >= 200 && [@AmountLastMonth] >= 200 )
VAR vResult =
    COUNTROWS ( vTableFilter )
RETURN
    vResult
    Spend under $80 = 
VAR vTableBase =
    ADDCOLUMNS (
        VALUES ( ClientSpend[Client Name] ),
        "@AmountCurrent", [Spend],
        "@AmountLastMonth", CALCULATE ( [Spend], DATEADD ( DimDate[Date], -1, MONTH ) ),
        "@AmountTwoMonthsAgo", CALCULATE ( [Spend], DATEADD ( DimDate[Date], -2, MONTH ) )
    )
VAR vTableFilter =
    FILTER (
        vTableBase,
        [@AmountCurrent] < 80
            && [@AmountLastMonth] >= 200
            && [@AmountTwoMonthsAgo] >= 200
    )
VAR vResult =
    COUNTROWS ( vTableFilter )
RETURN
    vResult
    Risk = 
VAR vNumerator =
    [Spend under $80]
VAR vDenominator =
    CALCULATE ( [Spend over $200], DATEADD ( DimDate[Date], -1, MONTH ) )
VAR vResult =
    DIVIDE ( vNumerator, vDenominator )
RETURN
    vResult

     

     

    In the second matrix, enable "Switch values to rows" to display measures as rows:

     

     

2 Replies

  • DataInsights's avatar
    DataInsights
    Super User
    3 years ago

    JJiso20,

     

    Try these measures:

     

    Spend = SUM ( ClientSpend[Spend$] )
    Spend over $200 = 
VAR vTableBase =
    ADDCOLUMNS (
        VALUES ( ClientSpend[Client Name] ),
        "@AmountCurrent", [Spend],
        "@AmountLastMonth", CALCULATE ( [Spend], DATEADD ( DimDate[Date], -1, MONTH ) )
    )
VAR vTableFilter =
    FILTER ( vTableBase, [@AmountCurrent] >= 200 && [@AmountLastMonth] >= 200 )
VAR vResult =
    COUNTROWS ( vTableFilter )
RETURN
    vResult
    Spend under $80 = 
VAR vTableBase =
    ADDCOLUMNS (
        VALUES ( ClientSpend[Client Name] ),
        "@AmountCurrent", [Spend],
        "@AmountLastMonth", CALCULATE ( [Spend], DATEADD ( DimDate[Date], -1, MONTH ) ),
        "@AmountTwoMonthsAgo", CALCULATE ( [Spend], DATEADD ( DimDate[Date], -2, MONTH ) )
    )
VAR vTableFilter =
    FILTER (
        vTableBase,
        [@AmountCurrent] < 80
            && [@AmountLastMonth] >= 200
            && [@AmountTwoMonthsAgo] >= 200
    )
VAR vResult =
    COUNTROWS ( vTableFilter )
RETURN
    vResult
    Risk = 
VAR vNumerator =
    [Spend under $80]
VAR vDenominator =
    CALCULATE ( [Spend over $200], DATEADD ( DimDate[Date], -1, MONTH ) )
VAR vResult =
    DIVIDE ( vNumerator, vDenominator )
RETURN
    vResult

     

     

    In the second matrix, enable "Switch values to rows" to display measures as rows:

     

     

  • JJiso20's avatar
    JJiso20
    Frequent Visitor
    3 years ago

    Thanks this is perfect! the help is much appreciated.