Measure to calculate percentage of a category from a column

[OPS-MLTSD]

Hello, I am trying to create a measure that gives me the percentage of two different categories from a column, this is what my table looks like:

Table name Organization

New Org Role | CA Records|

MCS                   5677

MLT                    3440            

I would like to create a measure for MCS where I can get the percentage total which is 62.27% for MCS, and same for MLT

I was wondering, is there a way to do that? Please note, I need to put the percentage information in a card visual, and since the "New Org Role" is a calculated column, I cannot just use the filter section, if someone could please let me know how I can use dax to create the measure for the card visual, that would be great

[vanessafvg]

you could do something like this

mcs% =

var tot = CALCULATE(sum('Table'[records]))

var mcs = CALCULATE(sum('Table'[records]), 'Table'[new org] = "mcs")

return CALCULATE(DIVIDE(mcs,tot))

[OPS-MLTSD]

just wondering, since the CA records are displayed like this:

CA Record

10005

1007

1009

1009

1010

would it this work?:

mcs% =

var tot = CALCULATE(Distinctcount('Table'[records]))

var mcs = CALCULATE(Distinctcount('Table'[records]), 'Table'[new org] = "mcs")

return CALCULATE(DIVIDE(mcs,tot))

[vanessafvg]

when you say the ca records look like this, i am not sure what you are asking, what is your concern?

the measure first sums all the records, then it sums the records by category ie mcs and then it divides the total of the category measure into the total giving you a %

maybe if you can explain a bit more why you are concerned and also just try create the measure and run it?  see what it does

[OPS-MLTSD]

it is giving me an error, when I try to run the code, this is what the data looks like:

When I do a distinct count of mcs and mlt, I get 5677, which is why I was wondering if I can do a DISTINCT count, but I am getting an error when I run the dax code, especially at the bottom: return CALCULATE(DIVIDE(mcs,tot)) <- is this how I should write the code?

[vanessafvg]

you could try this instead if you wanting to count the distinct id's of the ca records, make sure you create a measure.

mcs% =

var tot = CALCULATE(sumx(VALUES('Table'[records]),1))

var mcs = CALCULATE(SUMX(VALUES('Table'[records]),1), 'Table'[new org] = "mcs")

return CALCULATE(DIVIDE(mcs,tot))

[OPS-MLTSD]

I used the formula you have, this is what I am getting, is there a way to make this into 62% instead in the dax formula?

[vanessafvg]

click on the column on the right hand side and the select the following

[OPS-MLTSD]

thank you it works, but I was hoping for a calculation, that does not requre for the client to have to select any of the measure tools, I just have the calculations and when the measure is put in a card visual, it shows as 62.36%. Would this formula work?:

mcs% =

var tot = CALCULATE(sumx(VALUES('Table'[records]),1))

var mcs = CALCULATE(SUMX(VALUES('Table'[records]),1), 'Table'[new org] = "mcs")

return CALCULATE(DIVIDE(mcs,tot)*100)

[vanessafvg · Accepted solution]

try this

mcs% =
VAR tot =
    CALCULATE ( SUMX ( VALUES ( 'Table'[records] ), 1 ) )
VAR mcs =
    CALCULATE ( SUMX ( VALUES ( 'Table'[records] ), 1 ), 'Table'[new org] = "mcs" )
RETURN
    FORMAT ( CALCULATE ( DIVIDE ( mcs, tot ) ), "Percent" )

[OPS-MLTSD]

this works, thank you so much for your help! really appreciate it, could you explain this part of your formula:

mcs% =
VAR tot =
    CALCULATE ( SUMX ( VALUES ( 'Table'[records] ), 1 ) )
VAR mcs =
    CALCULATE ( SUMX ( VALUES ( 'Table'[records] ), 1 ), 'Table'[new org] = "mcs" )

why do you have to have 1 in the formula and sumx to calculate distinct values?

[vanessafvg]

this article explains the performance improvements when doing a distinct count with sumx vs distinctcount 

https://jlsql.blog/2018/02/06/sumx-vs-distinct-count/

however its not once fits all situations.  everything always boils down to testing if you have performance issues.

[OPS-MLTSD]

thank you!

[vanessafvg]

not quite sure i understand what you mean, this is a calculation.    When you say select the measure tools what do you mean, if you set this measure to a % it will always be a % when you drag it on to any visual.  You need to click on the measure and select what i showed you that way its always a %

otherwise Can you show me what you are wanting to do with pictures if that is not what you mean.

[OPS-MLTSD]

I was hoping to just write a formula that gives me an answer in this format -> 62% rather than this formate -> 0.62 without having to click on the column on the right hand side and the select what you showed on your screenshot above. 

[Ashish_Mathur]

Hi,

Click on the measure and in the ribbon, select %