Sum of Percentages of Subgrouping

[New_be]

Hi Everyone, 
i have a problem to get a correct sum of percentages for my project. I will show in details below;

I have a column named section as u can see in the image. Then i try to group this section like in another image below;

I also have a measure for YDA (yesterday), 

REJECT_YDA =

--

CALCULATE( DIVIDE( [TOTAL_REJECT], [TOTAL_PRD] ),

USERELATIONSHIP( 'Calendar'[DATE], RRB[Date (7-7)] ), 'Calendar'[Yesterday Equal Today] = TRUE() )

i use userelationship because in data model, this connection is inactive.

So as u can see, the Sum of percentage for TOTAL BLOCK C is incorrect because if we plus all the values inside the red circle, the correct value will be 0.19 %. Anyone know how to solve this? Really appreciate your solution & thanks in advance

[amitchandak]

@New_be , to me this seems correct formula, Can you put the [TOTAL_REJECT] and  [TOTAL_PRD] next to REJECT_YDA. Because one of them might have subtotal issue

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

[New_be]

https://www.youtube.com/watch?v=AJxCCfFmLTQ

i try to follow like in this video. But seems like i make some mistake. Below is my code;

My code: 

REJECT_YDA Test =

--

VAR SectionGroup = VALUES('Sections'[Section (groups)])

VAR Section = VALUES('Sections'[Section])

--

--

VAR TotalProduction =

SWITCH( TRUE(),

            ISINSCOPE('Sections'[Section (groups)]), CALCULATE([TOTAL_PRD], ALLSELECTED('Sections'), 'Sections'[Section         (groups)] IN SectionGroup, 'Sections'[Section] IN Section),

           ISINSCOPE('Sections'[Section]), CALCULATE( [TOTAL_PRD], ALLSELECTED('Sections') ) )

--

--

RETURN

IF( ISINSCOPE('Sections'[Section]),

        CALCULATE( DIVIDE( [TOTAL_REJECT], TotalProduction ),USERELATIONSHIP( 'Calendar'[DATE], RRB[Date (7-7)]        ),'Calendar'[Yesterday Equal Today] = TRUE()), 1)

But as u see, my value doesnt go as smooth as in the video

[v-shex-msft]

HI @New_be,

Can you please share a part of dummy data with a similar data structure and expected result? It will help to test coding formula on it. How to Get Your Question Answered Quickly 

In addition, you can also take a look at the following link about measure total calculations if it meets your requirement:

Measure Totals, The Final Word 

Regards,

Xiaoxin Sheng

[New_be]

Hi, here is my sample dummy data 
https://we.tl/t-J4nnwiFkxD

[harshnathani · Accepted solution]

Hi @New_be ,

Change your %Total Reject Measure to this.

% Total Reject =

SUMX(

Movement,

DIVIDE( [Total Reject], [Total PRD] )

)

Regards,

Harsh Nathani

Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

[New_be]

@harshnathani  Whit if i use a TOTALYTD function? can it also be calculate like that? 
Here is my YTD calculation;

REJECT_YTD =

VAR _max =

MAXX( ALLSELECTED(RRB), RRB[DATE_ORG] ) - TIME(07,00,00)

RETURN

TOTALYTD( DIVIDE([TOTAL_REJECT], [TOTAL_PRD]),

RRB[DATE_ORG], RRB[DATE_ORG] <= _max )

[New_be]

Hi, can i get your email for me to send u a dummy data?

[amitchandak]

@New_be , I see most of the values wrong as divide has some extra filter na relation.

Check with these two measures

CALCULATE( [TOTAL_REJECT] ,
USERELATIONSHIP( 'Calendar'[DATE], RRB[Date (7-7)] ), 'Calendar'[Yesterday Equal Today] = TRUE() )

CALCULATE([TOTAL_PRD] ,
USERELATIONSHIP( 'Calendar'[DATE], RRB[Date (7-7)] ), 'Calendar'[Yesterday Equal Today] = TRUE() )

[New_be]

Mr. Amit, here is the link to dummy data 
https://we.tl/t-J4nnwiFkxD

[New_be]

Hi Mr. Amitchandak,
can i get your email to send u a small dummy data in ppbix?

[New_be]

Here the values. But sum of percentages does not sum like the actual sum. For example, TOTAL BLOCK AB should get 0.05% instead of 0.03%