Need help to calculate % of view contributing 85%of total views per play

[atul06]

Hello ,

Below is Data 

PlayArea	PublishDateKey	ID	ContentViews	rank	Cumulative	Total Sum	cummulative 85 Perc	flag(Cumulative <Total Sum per Play Area)
Accelerate	20250404	123a	147	1	147	362	308	1
Accelerate	20250402	124b	65	2	212	362	308	1
Accelerate	20250425	125c	52	3	264	362	308	1
Accelerate	20250430	126d	36	4	300	362	308	1
Accelerate	20250410	127e	35	5	335	362	308	0
Accelerate	20250430	128f	27	6	362	362	308	0

Output Required 

Total Flag	Total Count	% cost  contributing 85% of total cost  per Solution Area
4	6	67%

Below Measures are created but its not working as expected

rank = ROWNUMBER(SUMMARIZE(ALLSELECTED(Play),

Play[PlayArea],Play[ID],"Sum_fin",[Total Content Views]),

ORDERBY([Sum_fin],DESC ),PARTITIONBY(Play[PlayArea]))

cumulative = 

VAR CurrentRank = [rank]

RETURN

    CALCULATE(

        SUM(Play[ContentViews]),

        FILTER(

            ALLSELECTED(Play),

            [rank] <= CurrentRank &&

            Play[PlayArea] = MAX(Play[PlayArea])

        )

    )

cummulative 85 Perc = 

VAR currentSolutionPlay = SELECTEDVALUE(Play[PlayArea])

VAR vTotalSP = CALCULATE(

    SUM(Play[ContentViews]),

    ALLSELECTED(Play),

    Play[PlayArea] = currentSolutionPlay 

)

VAR vTotalSP_85Per = vTotalSP 

RETURN

    vTotalSP_85Per*0.85

 Cumilativ flag = IF([cumulative]<[cummulative 85 Perc],1,0)

Cumulative flag is expanding rows, and its not giving correct results

[v-sshirivolu]

Hi @atul06 ,
Thanks for reaching out to the Microsoft fabric community forum.

Table name: Play

Step 1: Create Calculated Columns

1. RankPerPlayArea:
RANKX(
FILTER(Play, Play[PlayArea] = EARLIER(Play[PlayArea])),
Play[ContentViews],
,
DESC,
DENSE
)

2. CumulativeContentViews:
CALCULATE(
SUM(Play[ContentViews]),
FILTER(
Play,
Play[PlayArea] = EARLIER(Play[PlayArea]) &&
Play[RankPerPlayArea] <= EARLIER(Play[RankPerPlayArea])
)
)

3. Threshold85Percent:
CALCULATE(
SUM(Play[ContentViews]) * 0.85,
ALLEXCEPT(Play, Play[PlayArea])
)
\

4. CumulativeFlag:
IF(
Play[CumulativeContentViews] <= Play[Threshold85Percent],
1,
0
)

Step 2: Create Measures 

1. Total Flag:
CALCULATE(
COUNTROWS(Play),
Play[CumulativeFlag] = 1
)

2. Total Count:
COUNTROWS(Play)

3. % Contributing:
DIVIDE([Total Flag], [Total Count], 0)

Format this measure as Percentage.

Step 3: Visualize the Result

Create a Table visual or Cards:

Fields to Add:
PlayArea (Optional)
Total Flag
Total Count
% Contributing

Explanation of Each Component

RankPerPlayArea: Ranks rows by ContentViews within each PlayArea.
CumulativeContentViews: Adds up views row by row in descending order.
Threshold85Percent: 85% of total views per PlayArea.
CumulativeFlag: Marks rows that are part of first 85% contribution.
Measures: Aggregate totals and compute %.

Please find the below attached .pbix file for your reference.

If the response has addressed your query, please Accept it as a solution and give a 'Kudos' so other members can easily find it.

Best Regards,
Sreeteja.
Community Support Team 

[v-sshirivolu]

Hi @atul06  , 

Just wanted to check if you had the opportunity to review the suggestion provided?

If the response has addressed your query, please Accept it as a solution and give a 'Kudos' so other members can easily find it.

[v-sshirivolu]

Hi @atul06 ,

May I ask if you have resolved this issue? If so, please mark the helpful reply and Accept it as the solution and give a 'Kudos'. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[Ashish_Excel]

Hi,

Download the PBI file from here.

Hope this helps.

[atul06]

Hi @Ashish_Excel ,

Thanks for providng solution . When CV values are the same, the running total calculation becomes incorrect. Ranking should act like rownumber instead of dense rank dynamically

[v-sshirivolu]

Hi @atul06  ,
Thanks for reaching out to the Microsoft fabric community forum.

Try these steps to Resolve.

Use ROW_NUMBER like behavior

Replace the current [rank] measure with one that creates a unique sequence number even for ties i.e., simulating ROW_NUMBER in DAX.

1. Create a Unique Row Nymber like Measure
RowNumber Measure =
RANKX(
FILTER(
ALLSELECTED(Play),
Play[PlayArea] = MAX(Play[PlayArea])
),
[ContentViews]*100000 + VALUE(MAX(Play[PublishDateKey])),
,
DESC,
Skip
)

2. Cumulative Views Based on RowNumber

CumulativeViews Measure =

VAR CurrentRow = [RowNumber Measure]
RETURN
CALCULATE(
SUM(Play[ContentViews]),
FILTER(
ALLSELECTED(Play),
Play[PlayArea] = MAX(Play[PlayArea]) &&
[RowNumber Measure] <= CurrentRow
)
)

3. Total Views Per PlayArea
TotalViews Measure =
CALCULATE(
SUM(Play[ContentViews]),
ALLEXCEPT(Play, Play[PlayArea])
)

4. 85% Threshold
Threshold85 Measure = [TotalViews Measure] * 0.85

5. Flag Rows Contributing to 85%

Flag85 Measure =
IF(
[CumulativeViews Measure] < [Threshold85 Measure],
1,
0
)

6. % of Rows That Contribute to 85%

PercentContributing Measure =
DIVIDE(
CALCULATE(SUMX(VALUES(Play[ID]), [Flag85 Measure])),
CALCULATE(DISTINCTCOUNT(Play[ID]))
)

Im Attaching the file below for your reference

 

If the response has addressed your query, please Accept it as a solution and give a 'Kudos' so other members can easily find it

Best Regards,
Sreeteja.
Community Support Team 

[v-sshirivolu]

Hi @atul06 ,

May I ask if you have resolved this issue? If so, please mark the helpful reply and Accept it as the solution and give a 'Kudos'. This will be helpful for other community members who have similar problems to solve it faster.

[atul06]

No this is not working as expected

[v-sshirivolu]

Hi @atul06 ,
Try these steps -
Create Calculated Columns -
RankPerPlayArea:
RankPerPlayArea = RANKX( FILTER(Play, Play[PlayArea] = EARLIER(Play[PlayArea])), Play[ContentViews], , DESC, DENSE )
CumulativeContentViews:
CumulativeContentViews =
CALCULATE(
SUM(Play[ContentViews]),
FILTER(
Play,
Play[PlayArea] = EARLIER(Play[PlayArea]) &&
Play[RankPerPlayArea] <= EARLIER(Play[RankPerPlayArea])
)
)

Threshold85:
Threshold85 =
CALCULATE(
SUM(Play[ContentViews]) * 0.85,
ALLEXCEPT(Play, Play[PlayArea])
)
CumulativeFlag:
CumulativeFlag =
IF(Play[CumulativeContentViews] <= Play[Threshold85], 1, 0)

Create These Measures for Summary Visual -

TotalFlag :
TotalFlag= CALCULATE(COUNTROWS(Play), Play[CumulativeFlag] = 1)

TotalCount:
TotalCount = COUNTROWS(Play)

%Contributing:
PercentContributing =
DIVIDE([TotalFlag], [TotalCount])

Format PercentContributing as a percentage to clearly showcase your results.

I've verified that the issue is resolved, as my current output aligns perfectly with your expected output.
Find the attached .pbix file for your reference.

[v-sshirivolu]

Hi @atul06 
I hope the information provided above assists you in resolving the issue. If you have any additional questions or concerns, please do not hesitate to contact us. We are here to support you and will be happy to help with any further assistance you may need.

[v-sshirivolu]

Hi @atul06 ,
I hope the above details help you fix the issue. If you still have any questions or need more help, feel free to reach out. We’re always here to support you

[v-sshirivolu]

hi @atul06 ,

I hope the information provided above assists you in resolving the issue. If you have any additional questions or concerns, please do not hesitate to contact us. We are here to support you and will be happy to help with any further assistance you may need.

[ThxAlot]

Calculated columns are not necessary at all.

[atul06]

I have tried this solution. Its not working 

[ThxAlot]

Not surprised. For most rookies, it's even a challenge to copy an out-of-the-box solution to their own scenarios.