cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
abeiswinger Helper I
Helper I

Percentage of Total Metrics per Column (not Overall)

I would like the visual to take the total number of Inspections per month, add them, and show me the percentage (by month) of how many are on time.  When I use "show percentage" option, it shows overall instead of by month.  What kind of Measure formula can I use so that it calculates this for me?

 

My data with counts of Inspections:Inspection Percentages 1.png

What Power BI does:

Inspection Percentages 2.png

 

But I want it to say January = 79%, February =84%, etc.

January = 30/38

February = 38/45

etc.

 

If it helps... my spreadsheet/datasource is something like this:

Column A = Inspection number (unique identifier)
Column B = Date
Column C = On-Time or Past Target

3 ACCEPTED SOLUTIONS

Accepted Solutions
Interkoubess Solution Sage
Solution Sage

Re: Percentage of Total Metrics per Column (not Overall)

PastData.PNGPastData_.PNGHi @abeiswinger,

 

I reproduced you data and created this measure ( for details check pictures below):

 

Rat = DIVIDE(CALCULATE(SUM(TestData[OTT]),FILTER(TestData,TestData[Descr]="On Time")),CALCULATE(SUM(TestData[OTT])))

View solution in original post

Microsoft v-caliao-msft
Microsoft

Re: Percentage of Total Metrics per Column (not Overall)

@abeiswinger,

 

Please refer to the steps below to acheive your requirement. 

  1. Sample data.
    Capture.PNG
  2. Create a masure by using DAX below.
    Measure = CALCULATE(SUM(Table1[InspectionNumber]),FILTER(ALLEXCEPT(Table1,Table1[Month]),Table1[Type2]="On-Time"))/CALCULATE(SUM(Table1[InspectionNumber]),ALLEXCEPT(Table1,Table1[Month]))
  3. User this measure in your visual.
    Capture1.PNG

Regards,

Charlie Liao

View solution in original post

abeiswinger Helper I
Helper I

Re: Percentage of Total Metrics per Column (not Overall)

8 REPLIES 8
Interkoubess Solution Sage
Solution Sage

Re: Percentage of Total Metrics per Column (not Overall)

PastData.PNGPastData_.PNGHi @abeiswinger,

 

I reproduced you data and created this measure ( for details check pictures below):

 

Rat = DIVIDE(CALCULATE(SUM(TestData[OTT]),FILTER(TestData,TestData[Descr]="On Time")),CALCULATE(SUM(TestData[OTT])))

View solution in original post

abeiswinger Helper I
Helper I

Re: Percentage of Total Metrics per Column (not Overall)

@Interkoubess

 

Sorry, this formula is not working for me.  Can you please explain what each value is supposed to be?  I'm not sure where data is being pulled from:

 

Rat = DIVIDE(CALCULATE(SUM(TestData[OTT]),FILTER(TestData,TestData[Descr]="On Time")),CALCULATE(SUM(TestData[OTT])))

 

I'm assuming that anything that says [TestData[OTT] = the name of the tab (from Excel datasource) and the title of the column that I want to pull the "On-Time" or "Past Target" from?


So in the red, and blue, and green text... I change to mine.  However, what should I input for the blue bold text?  if I only put the tab name, I recieve an error message: MdxScript(Model) (3,46) Calculation error in measure 'WO Detail'[RAT]: The function SUM cannot work with the values of type String.

 

WO Detail is the name of my tab.

 

Interkoubess Solution Sage
Solution Sage

Re: Percentage of Total Metrics per Column (not Overall)

Hi @abeiswinger,

 

You are trying to sum up a string value.

 

'WO Detail'[RAT]: The function SUM cannot work with the values of type String.

 

Please change the type of data for example to Whole number or Decimal ones, etc...

From what you told me, the formula will be :

 

Rat = DIVIDE(CALCULATE(SUM('WO Detail'[RAT]:),FILTER('WO Detail','WO Detail'[the field you have on time]="On Time")),CALCULATE(SUM('WO Detail'[RAT])))

 

 

Furthermore a sample could help if you cannot apply this formula to your model.

 

Please dont hesitate if I can help.

 

Thx, Ninter

abeiswinger Helper I
Helper I

Re: Percentage of Total Metrics per Column (not Overall)

@Interkoubess

 

 What do you mean by "Please change the type of data for example to Whole number or Decimal ones, etc..."

Do I leave the [RAT] in the formula, or should that be changed as well?

 

Thanks

Interkoubess Solution Sage
Solution Sage

Re: Percentage of Total Metrics per Column (not Overall)

Hi @abeiswinger.

 

Your column named RAT is a string ( for example your name and surname you cannot add them as a sum).

And this is whqt you qre trying to do.

Select the column is PowerQuery for example and change the data type.

 

Let us know.

 

Thanks

Microsoft v-caliao-msft
Microsoft

Re: Percentage of Total Metrics per Column (not Overall)

@abeiswinger,

 

Please refer to the steps below to acheive your requirement. 

  1. Sample data.
    Capture.PNG
  2. Create a masure by using DAX below.
    Measure = CALCULATE(SUM(Table1[InspectionNumber]),FILTER(ALLEXCEPT(Table1,Table1[Month]),Table1[Type2]="On-Time"))/CALCULATE(SUM(Table1[InspectionNumber]),ALLEXCEPT(Table1,Table1[Month]))
  3. User this measure in your visual.
    Capture1.PNG

Regards,

Charlie Liao

View solution in original post

abeiswinger Helper I
Helper I

Re: Percentage of Total Metrics per Column (not Overall)

@v-caliao-msft

 

So close!  For some reason all my data now displays as the same?? Screenshot below of what it looks like with the measure, as well as what I have in my Visualizations... how can this be corrected?  My January data should be 79% and February should be 84%, etc...

 

abeiswinger Helper I
Helper I

Re: Percentage of Total Metrics per Column (not Overall)

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Join THE global Microsoft Power Platform event series.

Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors