Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
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)
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
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)
)
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.
@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?
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
144 | |
75 | |
64 | |
52 | |
47 |
User | Count |
---|---|
219 | |
89 | |
76 | |
67 | |
60 |