Forum Discussion

ShrikantKhanna's avatar
ShrikantKhanna
Icon for Helper II rankHelper II
9 years ago
Solved

Solution for COUNTIFS

Hi, I am trying to achieve a simple COUNTIFS solution in Power BI. I have read multiple messages but none of them are working out for me. I am trying to add a new column in my table and calculate th...
Need Help
  • Amratya's avatar
    Amratya
    9 years ago

    Please give this a try:

    Column D =
    CALCULATE (
        COUNTROWS ( 'TableName' ),
        ALLEXCEPT (
            'TableName',
            'TableName'[Team Name],
            'TableName'[Week #],
            'TableName'[Year]
        )
    )

  • v-huizhn-msft's avatar
    v-huizhn-msft
    9 years ago

    Hi ShrikantKhanna,

    You can use EARLIER function when you create calculated column. I used FILTER(Table5,Table5[Team Name]=EARLIER(Table5[Team Name])), which mean it will return the subtable with same Team name. Filter is compared to the criteria. Then I add multiple filters, so the formula will count [Team Name] where satisfy Filter1, Filter2, Filter3 and so on. 

    Result = CALCULATE(COUNTA(Table5[Team Name]),FILTER(Table5,Table5[Team Name]=EARLIER(Table5[Team Name])),FILTER(Table5,Table5[Week#]=EARLIER(Table5[Week#])),FILTER(Table5,Table5[Year]=EARLIER(Table5[Year])))


    Please review expected result in the following screenshot.

     

    If you have other issues, please feel free to ask.

    Best Regards,
    Angelia

MattAllington's avatar
MattAllington
Icon for Community Champion rankCommunity Champion
9 years ago

ShrikantKhanna your original approach is the correct one.  You should put the targets for each week in a second data table and join this table to common lookup tables.  

 

What is the issue with the slicer you mentioned?  Can you post a simple PBIX file (say dropbox link or something) with some sample data loaded and the tables all joined etc?  This makes it much easier to help you.

ShrikantKhanna's avatar
ShrikantKhanna
Icon for Helper II rankHelper II
9 years ago

MattAllington

I will create a sample and share with you in some time. There are 3-4 criterias that I have to include to try and replicate real scenario. 

In the mean time what I have done so far to fix my target issue is I have summarized my main table and dropped one criteria (out of 4) and called in target and sum of sales in it to create my presentation but I am really keen to create everything from my main table.

I will share with you as soon as I have create a sample for you to review.

 

Thanks!

 

and Thanks Amratya v-huizhn-msft