cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Frequent Visitor

Create a measure based on total column value

 

Hi all,

 

I'm trying to figure a way creating a measure based on the total amount column. So far the example below:

 

Client B= 11/58= 19% 

 

        
ClientYesNoDon’t KnowTotalDon’t know /Total column  
A20020%  
B391185819%  
C212385244%  
        

 

Any help would be appreciated, thanks

1 ACCEPTED SOLUTION

Hi @DL123 ,

The "Values" is the one that is put into the values field in Matrix.  Here is my data model.  

Data ModelData ModelIn MatrixIn Matrix

I attached my sample that you can reference. If this is not what you want, please point out the difference between your data model and mine?

 

Best Regards,

Xue Ding

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Kudos are nice too.

Best Regards,
Xue Ding
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

5 REPLIES 5
Community Support
Community Support

Hi @DL123 ,

You could use the function of DIVIDE to implement it. For your expected output, you want to calculate "No/Total". But in the sample data, you wrote "Don't know/Total column". I calculated both of them that you can have a try.

No/Total = DIVIDE(SUM('Table 1'[No]),SUM('Table 1'[Total]))
Don't know/Total = DIVIDE(SUM('Table 1'[Don’t Know]),SUM('Table 1'[Total]))

2.PNG

Best Regards,

Xue Ding

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Kudos are nice too.

 

Best Regards,
Xue Ding
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi @v-xuding-msft , thanks for taking the time to look at this.

 

However, im still having a problem with the using the divide function. How do i calculate if:

 

 

a) the columns are not a  separate field in the report,  but is  grouped .Eg  the "Yes" and "No" is  grouped under the field name of "Outcomes grouped". 

 

b)the "total column" is not a field , but rather is generated from the "Column subtotals" option in the visualisation.

 

Regards

Hi @DL123 ,

I guess that you use a matrix to show the value. I modify my table and create a new measure that you can have a try.

Measure = 
var subtotal = CALCULATE(SUM('Table'[Values]),ALLSELECTED('Table'[Outcomes grouped]))
var no = CALCULATE(SUM('Table'[Values]),FILTER('Table','Table'[Outcomes grouped] = "No"))
return
DIVIDE(no,subtotal)

1.PNG

Best Regards,

Xue Ding

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Kudos are nice too.

Best Regards,
Xue Ding
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

@v-xuding-msft  Thank you so much for the help, however i'm still a bit stuck!

 

In your measure , where can i find the  [Values] field in my posted example?

 

('Table'[Values])

 

Hi @DL123 ,

The "Values" is the one that is put into the values field in Matrix.  Here is my data model.  

Data ModelData ModelIn MatrixIn Matrix

I attached my sample that you can reference. If this is not what you want, please point out the difference between your data model and mine?

 

Best Regards,

Xue Ding

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Kudos are nice too.

Best Regards,
Xue Ding
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

Helpful resources

Announcements
secondImage

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

secondImage

Power BI Women

Join our monthly meetings and learning sessions.

secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors