Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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.

Reply
BenSchmidt
Frequent Visitor

Field parameter and "show value as percent of"

Hi everyone,

 

I totally LOVE the new field parameter feature and I created a report that is supposed to offer utmost flexibility to the viewer. However, I seem to get stuck with one actually simple requirement that I cannot get to work...

 

What I have:

One field parameter called "Parameter_Dimensions_Rows" that groups several dimensions (e.g. Category, Region, etc.). Another field parameter is called "Parameter_Dimensions_Columns" and it groups again several dimensions (e.g. Division, Business Unit, etc.) The third field parameter groups some measures (e.g. sales, no of orders, no of customers, etc.) and is called "Parameter_Measures". My report consists of a matrix that is set up with Columns=Parameter_Dimensions_Columns, Rows=Parameter_Dimensions_Rows and Values=Parameter_Measures. Next to the matrix I have three slicers representing each field parameter. By clicking in the slicer the user is able to dynamically and completely freely combine what is shown in the matrix in terms of the rows and the columns and the values. This setup works like a charm.

 

What I need:

In addition to the above mentioned measures I need to add percentage values for each of them being shown in the matrix.

 

What I tried:

1) At first I thought this should be simple by just right-clicking into the Values section of the matrix visual and selecting "show value as percent of ..." However, this option is not available. (see screenshot)

BenSchmidt_0-1656523563341.png

 

2) Then I thought to create the percentage measures using DAX formulas and then add them to the field parameter Parameter_Measures. However, I cannot figure out how the formula needs to look like since all my rows and columns are dynamically determined by the other field parameters.

Sales_Percent =
DIVIDE (
    CALCULATE (
        SUM ( Table[sales] );
        ALLSELECTED ( ???? )
    );
    CALCULATE ( SUM ( Table[sales] ), ???? )
)

 

So I got stuck 😞

I appreciate any help

 

Best wishes, Ben

6 REPLIES 6
crispybc
Frequent Visitor

I havent figured a way to get paramtised columns as a percentage yet. But if you using a single column value you could use GROUPBY in the measure. 

To build on previous:

Sales_Percent =
DIVIDE (
CALCULATE (
SUM ( Table[sales] )
),
CALCULATE ( SUM ( Table[sales] ), ALLSELECTED ( ), GROUPBY(table, column)
)

BenSchmidt
Frequent Visitor

Hi all,

does anyone else have an idea how to achieve the "show value as percent of column total" in this specific scenario with field parameters used for measures in a matrix visual? This is driving me crazy. All kind of help and guidance is appreciated.

Thanks, Ben 

I came across this issue today, and what @amitchandak mentioned works well. 

The way I used it was to create measures for all the values. In my case I wanted to show "Leads" and "Opportunities" as a percentage of total using custom parameters.

Therefore, I created two new measures "% Leads" and "% Opportunities". Which allowed me to add them into my custom parameter, giving me additonal options, I can now see absolute or % values. 

amitchandak
Super User
Super User

@BenSchmidt , Better to add measure

 

example

 

Sales_Percent =
DIVIDE (
CALCULATE (
SUM ( Table[sales] )
);
CALCULATE ( SUM ( Table[sales] ); ALLSELECTED ( ) )
)

 

 

Percent of SubTotal or Total: https://www.youtube.com/watch?v=6jTildcV2ho&list=PLPaNVDMhUXGaaqV92SBD5X2hk3TMNlHhb&index=37

Hi @amitchandak ,

 

thanks for your answer. I didn't see that ALLSELECTED doesn't need a parameter. Tried it and it works for the approach of "show values as percent of grand total". That's a big step forward for me.

 

Would you also have an idea how to achieve the other two scenarios: 1) "show value as percent of row total" and/or 2) "show value as percent of column total"?

 

That would be awesome.

Additional information:

I have tried the following approach in order to achieve the "show value as percent of column total", but unfortunately I get an error message

 

Sales pct2 =

DIVIDE(

CALCULATE(SUM(Table[sales])),

CALCULATE(SUM(Table[sales]),

ALLEXCEPT(Table,VALUES('Parameter_Dimensions_Columns'[Parameter_Dimensions_Columns Fields])))

)

The error message says: "The ALLEXCEPT function expects a table reference for argument '2', but a table expression was used

 

Is there a way to solve this?

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.