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

Shape the future of the Fabric Community! Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions. Take survey.

Reply
Vinod_G_245
Helper I
Helper I

Cumulative % Sum

Hi Team,

I struck with below Dax issue, Kindly help me in the cumulative dax mistake.

In Power BI I want to Show Cumulative  sum for Pareto chart. when i use allselected  function it is behaving differently because we have weightage for all filters but we want to consider only Filters which has error count Greater than zero.

Vinod_G_245_0-1722254723633.png

Cumulative Test =
 Var Total = CALCULATE([% of Total], ALLSELECTED('MCD Subcat'[Filter 1]))
Var _current = [% of Total]
Var _SumTable = SUMMARIZE(ALLSELECTED('MCD Subcat'), 'MCD Subcat'[Filter 1],"Errors",[% of Total])
Var Cumulativesum = SUMX(FILTER(_SumTable,[% of Total] >= _current),[% of Total])
Return
 DIVIDE( Cumulativesum, Total)
% of Total =
 DIVIDE( [X],
    CALCULATE(SUMX('MCD Subcat', [Filter 1 count] * 'MCD Subcat'[Weightage]),ALLSELECTED('MCD Subcat'[Filter 1])),0)
X = COUNT('MCD Subcat'[Week])* MAX('MCD Subcat'[Weightage])

 

5 REPLIES 5
Vinod_G_245
Helper I
Helper I

Hi All,

Can anyone help me how to get above cumulative % sum by using below two tables.  Getting cumulative % on filter is becoming challenge.

Vinod_G_245
Helper I
Helper I

Hi @Greg_Deckler : Thank you for response, By using above data created first Posted table.

Vinod_G_245
Helper I
Helper I

Table-1(Fact Table)

MonthWeekFilter 1
JunWeek 3Contact_Result
JunWeek 3Drop_down_Boxes
JunWeek 3Dead_Air
JunWeek 3Contact_Result
JunWeek 3Disclaimer_Documented
JunWeek 3Communication_Record
JunWeek 3Disclaimer_Documented
JunWeek 3Hold_Time
JunWeek 3Communication_Record
JunWeek 3Requesting_Provider
JunWeek 3Authentication_Documented
JunWeek 3Correct_Complete_Attachment
JunWeek 3Call_Recorded_Statement_Documented
JunWeek 3Accurate_Complete_Info
JunWeek 3Dead_Air
JunWeek 2Requesting_Provider
JunWeek 2Disclaimer_Documented
JunWeek 2Communication_Record
JunWeek 2Disclaimer_Documented
JunWeek 2Dead_Air
JunWeek 2Call_Recorded_Statement_Documented
JunWeek 2Dead_Air
JunWeek 2Dead_Air
JunWeek 2Dead_Air
JunWeek 2Member
JunWeek 2Disclaimer_Verbally
JunWeek 1Communication_Record
JunWeek 1Drop_down_Boxes
JunWeek 1Dead_Air
JunWeek 1Disclaimer_Documented
JunWeek 1Contact_Result
JunWeek 1Courteous_Engaged
JunWeek 1Courteous_Engaged
JunWeek 1Accurate_Complete_Info
JunWeek 1Disclaimer_Documented
JunWeek 1Disclaimer_Documented
JunWeek 1Drop_down_Boxes
JunWeek 1Call_Recorded_Statement_Documented
JunWeek 1Disclaimer_Verbally
JunWeek 1Dead_Air
JunWeek 1Correct_Complete_Attachment
JunWeek 1Drop_down_Boxes
JunWeek 1Disclaimer_Verbally

Table-2 (Weight to multiply the error count)

Identify_Self3
Call_Recorded_Statement_Verbal3
Call_Recorded_Statement_Documented3
Authentication_Verbal3
Authentication_Documented3
Notification_of_Determination10
Contact_Result5
Disclaimer_Verbally5
Disclaimer_Documented3
Requesting_Provider3
Treating_Provider3
Facility3
Member3
Drop_down_boxes3
Requested_Authorized_Units3
Type_of_Units3
Service_First_Last_Day3
Notification_Date3
Request_Type3
DX_Code3
CPT_Code3
Process_Timely3
Expedited5
Correct_Filename_Attachment3
Correct_Sent_Received_Attachment3
Correct_Complete_Attachment5
Correct_Fax_Number_sent_entered3
Communication_Record5
Task3
Dead_Air3
Hold_Time3
Professional_Tone_Language3
Courteous_Engaged3
Accurate_Complete_Info10

Table 3- Error Count Calculation In pivot

Row Labels

 

Count of Week

Accurate_Complete_Info2
Authentication_Documented1
Call_Recorded_Statement_Documented3
Communication_Record4
Contact_Result3
Correct_Complete_Attachment2
Courteous_Engaged2
Dead_Air8
Disclaimer_Documented7
Disclaimer_Verbally3
Drop_down_Boxes4
Hold_Time1
Member1
Requesting_Provider2
Grand Total43
Greg_Deckler
Super User
Super User

@Vinod_G_245 Is the image what you are getting or what you want to achieve? Can you post sample data? 

 

Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...
Vinod_G_245
Helper I
Helper I

Kindly ping here if need additional information

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

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

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.