cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

How to calculate inter and intra in Power BI?

Capture.PNG

 

Hi guys,

 

I need help with calculating the inter and intra in Power Bi. From the screenshot, I created this in excel, and this is how I should calculate everything. On the top chart is the final result, and the bottom chart is the data that I need to calculate to define Inter and Intra.

 

And I tried my best to explain everything in the note. The title is the most important column to filter or to calculate everything. And the reason I use colors because it depends on which title is in the program. It is very complicated for me, and I hope everything makes sense.

 

Thanks

1 ACCEPTED SOLUTION

Hi @Anonymous ,

 

We can use the following measures without RemoveFilter function:

 

Intra = 
VAR t0 =
    CALCULATETABLE ( ALLSELECTED ( 'Table' ),ALL('Table'[Programs]) )
RETURN
    SUMX (
        DISTINCT ( 'Table'[Programs] ),
        COUNTROWS (
            FILTER (
                SUMMARIZE (
                    t0,
                    'Table'[Title],
                    "Count", DISTINCTCOUNT ( 'Table'[Programs] ),
                    "TotalRow", COUNTROWS ( 'Table' )
                ),
                VAR t = [Title] RETURN [Count] = 1
                    && EARLIER ( 'Table'[Programs] )
                        IN SELECTCOLUMNS ( FILTER ( t0, 'Table'[Title] = t ), "T", [Programs] )
            )
        )
    )

 

Inter and Intra = 
VAR t0 =
    CALCULATETABLE(ALLSELECTED(  'Table' ),ALL('Table'[Programs]))
RETURN
    SUMX (
        DISTINCT ( 'Table'[Programs] ),
COUNTROWS (
    FILTER (
        SUMMARIZE( 
            t0 ,
            'Table'[Title],"Count",DISTINCTCOUNT('Table'[Programs]),"TotalRow",COUNTROWS('Table')
        ),
        var t = [Title] return [Count]>1 && [Count]<>[TotalRow]  && EARLIER ( 'Table'[Programs] )
                        IN SELECTCOLUMNS ( FILTER ( t0, 'Table'[Title] = t ), "T", [Programs] )
            )
        )
    )

 

Inter = 
VAR t0 =
    CALCULATETABLE(ALLSELECTED(  'Table' ),ALL('Table'[Programs]))
RETURN
    SUMX (
        DISTINCT ( 'Table'[Programs] ),
        COUNTROWS (
            FILTER (
                SUMMARIZE (
                    t0,
                    'Table'[Title],
                    "Count", DISTINCTCOUNT ( 'Table'[Programs] ),
                    "TotalRow", COUNTROWS ( 'Table' )
                ),
                VAR t = [Title] RETURN [Count] > 1
                    && EARLIER ( 'Table'[Programs] )
                        IN SELECTCOLUMNS ( FILTER ( t0, 'Table'[Title] = t ), "T", [Programs] )
            )
        )
    )

 


Best regards,

 

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

17 REPLIES 17
Solution Sage
Solution Sage

it seems like there are 2 rules operating here
1) if a title appears in more than  1 program its inter

2) a title that appears more than once with the same program is intra
what you don't explain is what divides the entries into groups...the rules only apply for entries between the green lines

there must be some id or counter that flags which rows apply to each group of entries

 

If you can confirm this I can write the dax to do the calculation for you

 

 





Did this post answer your question? Mark it as a solution so others can find it!

Help when you know. Ask when you don't!




Join the conversation at We Talk BI find out more about me at Slow BI


Anonymous
Not applicable

First, I want to say thank you so much for trying to help me. I've been stuck with this problem for days and still couldn't figure it out.

 

Yes, correct. There are a person IDs as well. I'm working on a project that requires me to calculate how many authors published an article(s), and they're also in different departments (programs).

 

What divided them into Inter, Intra, or inter and Intra are depended on,

- if the person name who published an article with the title named "abc" and he/she is associated with program 1, it will count as Intra. (As long as with the same title and with the same program name it will count as Intra, it does not matter how many people are in this same program.),

- and if multiple persons and from multiple programs but with the same title, and if this title is associated with different programs (like program 1 and program 5) will count as Inter. (If the title has authors (person) from >1 program then it would be considered Inter)

 

Rules:

- Intra: More than 1 author from the same program on the same title.

- Inter: More than 1 program on the title (e.g. two authors: one from program 1, one from program 5)

 

I hope everything makes sense, and I really hope you can help me figure this out. Thank you so much for responding to my post.

Hi @Anonymous ,

 

We can create three measures to meet you requirement, but we cannot understand why the two inter sub-table are divided to two different program although the two sub-tables have the same construction.

 

Inter = 
COUNTROWS (
    FILTER (
        SUMMARIZE( 
            ALLSELECTED('Table') ,
            'Table'[Title],"Count",DISTINCTCOUNT('Table'[Programs]),"TotalRow",COUNTROWS('Table')
        ),
        var t = [Title] return [Count]>1 && [Count]=[TotalRow] && CALCULATE(MAX('Table'[Programs]),FILTER(ALLSELECTED('Table'),'Table'[Title]=t)) in FILTERS('Table'[Programs])
    )
)

 

Intra = 
COUNTROWS (
    FILTER (
        SUMMARIZE( 
            ALLSELECTED('Table') ,
            'Table'[Title],"Count",DISTINCTCOUNT('Table'[Programs]),"TotalRow",COUNTROWS('Table')
        ),
        var t = [Title] return [Count]=1 && CALCULATE(MAX('Table'[Programs]),FILTER(ALLSELECTED('Table'),'Table'[Title]=t)) in FILTERS('Table'[Programs])
    )
)

 

Inter and Intra = 
COUNTROWS (
    FILTER (
        SUMMARIZE( 
            ALLSELECTED('Table') ,
            'Table'[Title],"Count",DISTINCTCOUNT('Table'[Programs]),"TotalRow",COUNTROWS('Table')
        ),
        var t = [Title] return [Count]>1 && [Count]<>[TotalRow] && CALCULATE(MAX('Table'[Programs]),FILTER(ALLSELECTED('Table'),'Table'[Title]=t)) in FILTERS('Table'[Programs])
    )
)

 

9.jpg


Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

Do you know why is not showing the correct numbers after I click on the programs? Inter and Intra is blank 

Hi @Anonymous ,

 

Sorry for late reply, we are trying to correct the formula we have shared, we will update here as soon as possible.


Best regards,

 

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

Ok, thank you.

I have like 10K+ rows for this project, and I was trying to fix the code and no matter what I do I can't figure it out because the number just don't add up right.

Hi @Anonymous ,

 

Could you please share the detail logic that why "abc" and "yui" are assigned to different Program although they have similar data? 

 

8.jpg

 

Could you please provide a more complex mockup table based on fake data? Please don't have any Confidential Information or Real data in your reply.


Best regards,

 

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

Capture.PNG

 

Hi @v-lid-msft ,

My mistakes, I updated the data and rules, please take a look at the screenshot and hope everything makes sense. The person who created this data has left the team, so I do not know the logic behind the code; otherwise, this would be much easier. But in the data he/she did use an asterisk (*) to indicate the difference, please look at the highlighted portion in the screenshot. And I changed the names, so it is easier to understand, they are the same people but published in different articles. And the color cells are to help you read it easier.

 

The rules are:

  • 1 person can only associate with 1 program,
  • but 1 person can publish multiple articles (title)

 

For Inter:

Jason L and Garry C both are in different programs, but they both published the same article (title) called: abc. Because they both are in different programs, so each program will get 1 count, the total will be equal to 2 Inter. Same for Kelly M and Peter K, and that adds up to 4 Inter total.

 

For Intra:

Since everyone is in the same group and all of them published the same article (title), this will count as 1 Intra. And if there is another article (title) named: abcde, and with the same program like program 21, it will count as Intra and will add up to the existing one so that the total will equal to 2 Intra.

 

For Inter and Intra:

This one gets a little complicated. But basically, if the person published an article that was list in Inter OR Intra, then they will be combined together and count as Inter AND Intra.

 

Inter and Intra example:

Max E. and Kai P. are in program 1, and if you look at the Intra section, they already listed as Intra when they published the article called xyz. And for Garry C. and Peter K., they were already listed in Inter above, when Garry C published abc, opq, and efg, and Peter K published yui;

This means in "Inter and Intra," it will combine them from Inter and Intra, and program 1 count as one point, and even though program 5 is showing twice with two different people, this will still count as one point as well, I do not know why but that's how the logic is.

Capture2.PNG

 

In the real data, each article has more than 2 or 3 persons published. Like for title abc, right now, I only listed Jason L and Garry C, but in reality, few more people published the same article like, Josh, Lee, Heather, etc. 

 

Thank you for helping me with this, and I really appreciate it.

Hi @Anonymous ,

 

Thank you for detail logic very much, it is very helpful,  please try to use the following measures:

 

Inter = 
VAR t0 =
    CALCULATETABLE(ALLSELECTED(  'Table' ),REMOVEFILTERS('Table'[Programs]))
RETURN
    SUMX (
        DISTINCT ( 'Table'[Programs] ),
        COUNTROWS (
            FILTER (
                SUMMARIZE (
                    t0,
                    'Table'[Title],
                    "Count", DISTINCTCOUNT ( 'Table'[Programs] ),
                    "TotalRow", COUNTROWS ( 'Table' )
                ),
                VAR t = [Title] RETURN [Count] > 1
                    && EARLIER ( 'Table'[Programs] )
                        IN SELECTCOLUMNS ( FILTER ( t0, 'Table'[Title] = t ), "T", [Programs] )
            )
        )
    )

 

Intra = 
VAR t0 =
    CALCULATETABLE ( ALLSELECTED ( 'Table' ), REMOVEFILTERS ( 'Table'[Programs] ) )
RETURN
    SUMX (
        DISTINCT ( 'Table'[Programs] ),
        COUNTROWS (
            FILTER (
                SUMMARIZE (
                    t0,
                    'Table'[Title],
                    "Count", DISTINCTCOUNT ( 'Table'[Programs] ),
                    "TotalRow", COUNTROWS ( 'Table' )
                ),
                VAR t = [Title] RETURN [Count] = 1
                    && EARLIER ( 'Table'[Programs] )
                        IN SELECTCOLUMNS ( FILTER ( t0, 'Table'[Title] = t ), "T", [Programs] )
            )
        )
    )

 

Inter and Intra = 
VAR t0 =
    CALCULATETABLE(ALLSELECTED(  'Table' ),REMOVEFILTERS('Table'[Programs]))
RETURN
    SUMX (
        DISTINCT ( 'Table'[Programs] ),
COUNTROWS (
    FILTER (
        SUMMARIZE( 
            t0 ,
            'Table'[Title],"Count",DISTINCTCOUNT('Table'[Programs]),"TotalRow",COUNTROWS('Table')
        ),
        var t = [Title] return [Count]>1 && [Count]<>[TotalRow]  && EARLIER ( 'Table'[Programs] )
                        IN SELECTCOLUMNS ( FILTER ( t0, 'Table'[Title] = t ), "T", [Programs] )
            )
        )
    )

10.jpg11.jpg

 


Best regards,

 

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

Hi @v-lid-msft 

 

Thank you very much for replying. For some reason I'm getting the same error for Inter, Intra, and Inter and Intra. Besides the numbers (29, 52). Also, in DAX, I cannot use "removefilters", it says: Cannot find name "removefilters". Removefilters is not a function. I have to use this version.

 

My Power BI Version: Version: 2.69.5467.5201 64-bit (May, 2019)

2019-12-26_8-46-45.png

 

error 3.png

 

error 2.png

Hi @Anonymous ,

 

We can use the following measures without RemoveFilter function:

 

Intra = 
VAR t0 =
    CALCULATETABLE ( ALLSELECTED ( 'Table' ),ALL('Table'[Programs]) )
RETURN
    SUMX (
        DISTINCT ( 'Table'[Programs] ),
        COUNTROWS (
            FILTER (
                SUMMARIZE (
                    t0,
                    'Table'[Title],
                    "Count", DISTINCTCOUNT ( 'Table'[Programs] ),
                    "TotalRow", COUNTROWS ( 'Table' )
                ),
                VAR t = [Title] RETURN [Count] = 1
                    && EARLIER ( 'Table'[Programs] )
                        IN SELECTCOLUMNS ( FILTER ( t0, 'Table'[Title] = t ), "T", [Programs] )
            )
        )
    )

 

Inter and Intra = 
VAR t0 =
    CALCULATETABLE(ALLSELECTED(  'Table' ),ALL('Table'[Programs]))
RETURN
    SUMX (
        DISTINCT ( 'Table'[Programs] ),
COUNTROWS (
    FILTER (
        SUMMARIZE( 
            t0 ,
            'Table'[Title],"Count",DISTINCTCOUNT('Table'[Programs]),"TotalRow",COUNTROWS('Table')
        ),
        var t = [Title] return [Count]>1 && [Count]<>[TotalRow]  && EARLIER ( 'Table'[Programs] )
                        IN SELECTCOLUMNS ( FILTER ( t0, 'Table'[Title] = t ), "T", [Programs] )
            )
        )
    )

 

Inter = 
VAR t0 =
    CALCULATETABLE(ALLSELECTED(  'Table' ),ALL('Table'[Programs]))
RETURN
    SUMX (
        DISTINCT ( 'Table'[Programs] ),
        COUNTROWS (
            FILTER (
                SUMMARIZE (
                    t0,
                    'Table'[Title],
                    "Count", DISTINCTCOUNT ( 'Table'[Programs] ),
                    "TotalRow", COUNTROWS ( 'Table' )
                ),
                VAR t = [Title] RETURN [Count] > 1
                    && EARLIER ( 'Table'[Programs] )
                        IN SELECTCOLUMNS ( FILTER ( t0, 'Table'[Title] = t ), "T", [Programs] )
            )
        )
    )

 


Best regards,

 

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

It's a complex topic. I'll repeat my offer of a screen share. I think I can solve the problem if I understand more of what you want.
Send me and email with a day and time and I'll set up a Zoom session.

I'm ken@8thfold.com





Did this post answer your question? Mark it as a solution so others can find it!

Help when you know. Ask when you don't!




Join the conversation at We Talk BI find out more about me at Slow BI


Anonymous
Not applicable

Hi,

 

Do you know why when I clicked on the programs to filter in visual, Inter and "Inter and Intra" are always blank?

Anonymous
Not applicable

Oh the two inter sub-table were just another example, cause there were like thousand of data so I thought i'll add few more example would help others to understand my questions better. Haha, didn't mean to confuse you. And I'll try to work on the solution you have provided.

 

Thank you

Can an author be in more than one program... it looks like no, from your example
so for intra the algorithm is

count the number of authors per program a title is listed under... if any of the programs have a count of more than 1 its "intra"

Count the number of programs a title is listed under, if its more than 1 its "inter" (since people are only in 1 program, counting programs is all we have to do)


what do you call it if a title has only one author/program ?





Did this post answer your question? Mark it as a solution so others can find it!

Help when you know. Ask when you don't!




Join the conversation at We Talk BI find out more about me at Slow BI


It might help to do a screen share on this.

Send me your email and a good time for you to do it tomorrow, and I'll send you an invidation to a zoom meeting
I'm in PST and I'm available from about 8:00 an to 6:00 pm

 





Did this post answer your question? Mark it as a solution so others can find it!

Help when you know. Ask when you don't!




Join the conversation at We Talk BI find out more about me at Slow BI


Anonymous
Not applicable

Hi,

 

I work during that time and I'm not allow to do that at work, and cannot share work data with anyone. The one I created are just similar. Do you think you can try to post a solution on here? I appreciate your helps.

 

Thanks

Helpful resources

Announcements
secondImage

Happy New Year from Power BI

This is a must watch for a message from Power BI!

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

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

Top Solution Authors
Top Kudoed Authors