Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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.


Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension!

DAX is simple, but NOT EASY!

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.


Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension!

DAX is simple, but NOT EASY!

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.

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.