cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Saxon10
Post Prodigy
Post Prodigy

SUM(INDEX(1/COUNTIFS in Power BI

Hi,

 

How can I do sum and countifs in Power BI.

 

I have a following column are item, id type, colour code text in my Table and I would like to get the unique count based those columns.

I am able to count by using below mentioned DAX code but I don't how can I do sum and index in my exciting DAX code.

countifs =
VAR ITEEM = Data[Item]
VAR IDTYPE =Data[Id Type]
VAR CCTEXT = Data[Colour Code Text]
RETURN
COUNTROWS(FILTER(Data,
[Item]=ITEEM && [Id Type]=IDTYPE && [Colour Code Text]=CCTEXT
))
 

In Excel I am applying the following formula and I would like to get the same thing in PBI. Could you please provide the DAX solution.

 

D3=SUM(INDEX(1/COUNTIFS(A$3:A$101,A3,B$3:B$101,B3,C$3:C$101,C3),0))

 

Herewith attached the PBI for your reference.

 

Saxon10_0-1632489037442.png

 

 

Saxon10_1-1632489385377.png

 

https://www.dropbox.com/s/6viom43bcbfrklo/SUM%20INDEX%20COUNTIFS.pbix?dl=0

 

1 ACCEPTED SOLUTION
CNENFRNL
Community Champion
Community Champion

@Saxon10 ,for your reference,

 

=  1 / COUNTROWS(
        FILTER(
            'Table',
            'Table'[Item] = EARLIER( 'Table'[Item] )
                && 'Table'[Id Type] = EARLIER( 'Table'[Id Type] )
                && 'Table'[Colour Code Text] = EARLIER( 'Table'[Colour Code Text] )
        )
    )

 

 

Another piece of advice on Excel formula, using Structured Reference makes formula readable and dynamic. Structured Reference is one of the most elegant features of Excel.

View solution in original post

4 REPLIES 4
CNENFRNL
Community Champion
Community Champion

@Saxon10 ,for your reference,

 

=  1 / COUNTROWS(
        FILTER(
            'Table',
            'Table'[Item] = EARLIER( 'Table'[Item] )
                && 'Table'[Id Type] = EARLIER( 'Table'[Id Type] )
                && 'Table'[Colour Code Text] = EARLIER( 'Table'[Colour Code Text] )
        )
    )

 

 

Another piece of advice on Excel formula, using Structured Reference makes formula readable and dynamic. Structured Reference is one of the most elegant features of Excel.

View solution in original post

Thanks for your reply and help.

Your solution is working well. 

I try to make structured references formula so it's easy to read it. 

amitchandak
Super User
Super User

@Saxon10 ,Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin

Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!

Thanks for your reply. Here is the data for your reference.

 

Herewith attached the PBI for your reference 

https://www.dropbox.com/s/6viom43bcbfrklo/SUM%20INDEX%20COUNTIFS.pbix?dl=0

 

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!