Percent of the total value - DAX Formula

[cn4422]

Hello,

when we put something into "value" (see screenshot) there is the option to show the value as... "percent of the total value "column"".

How would I put this as a Dax Formula?

For the value itself I use the following:

Count Forms Category =
COUNT('lead'[Forms category])

The idea is that I have two separate "values" because if I use the first value for both number and per cent, it's basically the same data point.

But I have no idea how I would adjust this...

Thanks for your help! 🙂

[BITomS]

Hi @cn4422 ,

Your explicit % measure could be:

% Measure = divide( COUNT('lead'[Forms category]) , CALCULATE ( COUNT('lead'[Forms category]) ) , ALL ('Lead') , 0 )

This removes the row context from the denominator within the % calculation, so you are dividing the row count by the total count.

Hope this helps!

[cn4422]

@BITomS 

Thanks for your reply!

I've tried your suggested measure and here's the result.

In green I have marked the correct "automatic calculation" (which is the goal) and in red you can see the result of the measure.

I think to remove all filters could be problematic...

The formula should be something like: 

% Measure = divide( COUNT('lead'[Forms category]) , SUM(Count of column Country), 0 )

But I don't know how to put this "sum of Coun of Column of each Country" into a formula...

[v-yiruan-msft · Accepted solution]

Hi @cn4422 ,

You can create two measures as below to get it, please find the details in the attachment.

Count Form Category = COUNT('lead'[Forms category])

% Measure =
DIVIDE (
    [Count Form Category],
    SUMX ( ALLSELECTED ( 'lead'[Contact group] ), [Count Form Category] ),
    0
)

Best Regards

[cn4422]

@v-yiruan-msft Many thanks for your reply and your effort!

I've tried to recreate the matrix in your file and it worked.

However, in my file, the %-measure only works for the total-column. In the other columns it's always showing 100% (please see screesnhot).

The only difference I can fathom is that in my report I don't have a "static table" but I use the dataverse to access the data... don't know if this might be an issue?

[v-yiruan-msft]

Hi @cn4422 ，

Thanks for your feedback. It seems like the returned result is not correct when you applied the same formula in your model table. Could you please provide some sample data for the fields which apply in your matrix visual? Are these fields in the matrix from multiple tables? It would be helpful to find out the solution. You can refer the following links to share the required info:

How to provide sample data in the Power BI Forum

And It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards

[cn4422]

@v-yiruan-msft thanks again for your help!

I tried a few things because it was bugging me why it wouldn't work in my model table... finally I built my model table again in a new file and there it worked! 😍 Seems like the other model was somehow broken... never mind.

One more thing: With "rules" it works fine - but do you happen to know if it's possible to use the format style "gradient" for each column alone as well? And not the total matrix table as reference?

[v-yiruan-msft]

Hi @cn4422 ,

It's glad to hear that your original problem has been resolved. I'm not clear about the following sentence, could you please provide some sample data and explain more details(scenario, logic and expected result etc.) on it. Thank you.

---

if it's possible to use the format style "gradient" for each column alone as well? And not the total matrix table as reference?

---

Best Regards

[cn4422]

Hi @v-yiruan-msft 

Perhaps this screenshot illustrates better what I have in mind:

At the top of the screenshot, the gradient formatting includes the values of the whole matrix table as reference, therefore we have the highest number (1.530) in green and lowest number (3) in red.

At the bottom we have the same matrix table, but the gradient formatting is restricted to the numbers of every single column, meaning every column has it's own highest and lowest value.

Do you see what I mean?

[v-yiruan-msft]

Hi @cn4422 ,

I updated my sample pbix file(see the attachment), please check if that is what you want.

1. Create a measure as below

MinMax = 
VAR Vals = 
    CALCULATETABLE(
        ADDCOLUMNS (
            SUMMARIZE ( Sales, 'Product'[Brand], Store[Continent] ),
            "@SalesAmt", [Sales Amount]
        ),
        ALLSELECTED ()
    )
VAR MinValue = MINX ( Vals, [@SalesAmt] )
VAR MaxValue = MAXX ( Vals, [@SalesAmt] )
VAR CurrentValue = [Sales Amount]
VAR Result = 
    SWITCH ( 
        TRUE,
        CurrentValue = MinValue, 1, -- 1 for MIN
        CurrentValue = MaxValue, 2  -- 2 for MAX
    )
RETURN
    Result

2. Apply conditional formatting with backgroud color

Best Regards

[cn4422]

@v-yiruan-msft Many thanks for your reply!

Very interesting approach!

We have 3 colors based on the 3 values 1, 2, 3 - that works just fine if you want to define min, max and all in between with 3 colors. However, it's not really "gradient" since it is only 3 colors.

Do you know what I mean?

In this example, there are several color gradations.