Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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" )
Proud to be a Super User!
you could do something like this
Proud to be a Super User!
just wondering, since the CA records are displayed like this:
CA Record
10005
1007
1009
1009
1010
would it this work?:
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
Proud to be a Super User!
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?
you could try this instead if you wanting to count the distinct id's of the ca records, make sure you create a measure.
Proud to be a Super User!
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?
click on the column on the right hand side and the select the following
Proud to be a Super User!
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?:
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" )
Proud to be a Super User!
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?
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.
Proud to be a Super User!
thank you!
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.
Proud to be a Super User!
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.
Hi,
Click on the measure and in the ribbon, select %
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
96 | |
93 | |
83 | |
70 | |
65 |
User | Count |
---|---|
118 | |
106 | |
93 | |
79 | |
72 |