Forum Discussion
Helper IIFinancial Reporting Matrix Profitbase NEED HELP
Hello i need some help with the visual "FInancial Rpeorting Matrix By Profit base"
i want te make a tabel with 3 colums like the table below. i need some help with my total Colum and my % colum. i need to make those 2 colums myself. i want to have a new total colum that the values will be empty and it gives me a total of the valaus of description same as my % colum.
i think it has to something like this
IF(RowLevel() = "Total", "value $", blank())
so "trading deals" is the name etc etc
Hi Krijgersss
To achieve this, you need to use DAX measures that check whether the current row is a detail row or a total row, using the HASONEVALUE function. If it’s a detail row, the measure returns blank; if it’s a total row, the measure returns the sum or percentage. See below code references.
Total = IF( HASONEVALUE('Table'[Description]), BLANK(), SUM('Table'[value $]) )Percent = IF( HASONEVALUE('Table'[Description]), BLANK(), 30 // Replace 30 with your own calculation if needed )
8 Replies
- bhanu_gautam
Super User
KrijgersssY ou can create a calculated column that will show the total value for each description. Use the following
DAX
Total =
IF(
ISINSCOPE('YourTable'[Description]),
BLANK(),
SUM('YourTable'[value $])
)You can create another calculated column to calculate the percentage of each value relative to the total. Use the following DAX formula:
DAX
% =
IF(
ISINSCOPE('YourTable'[Description]),
BLANK(),
DIVIDE(SUM('YourTable'[value $]), CALCULATE(SUM('YourTable'[value $]), ALL('YourTable')))
)Add a matrix visual to your report and place the "Description" in the Rows, "value $" in the Values, and the newly created "Total" and "%" columns in the Values as well.
Krijgersssthis wont work for me because i have to use the Financial reporting matrix by profit base because my total row has to be made by hand just as in excel to "add a Row after"
- rohit1991
Hi Krijgersss
To achieve this, you need to use DAX measures that check whether the current row is a detail row or a total row, using the HASONEVALUE function. If it’s a detail row, the measure returns blank; if it’s a total row, the measure returns the sum or percentage. See below code references.
Total = IF( HASONEVALUE('Table'[Description]), BLANK(), SUM('Table'[value $]) )Percent = IF( HASONEVALUE('Table'[Description]), BLANK(), 30 // Replace 30 with your own calculation if needed )
v-aatheequeCommunity Support
Hi Krijgersss ,
Thanks for reaching out to Fabric Community Forum.
rohit1991 Thanks for your prompt response. In addition to that let me add more insights,
Following the DAX solution shared earlier by rohit1991 , I’ve implemented it on my side. Here is the expected output along with the attached PBIX file.Hope this helps !!
- v-aatheeque
Hi Krijgersss
Just a quick reminder your query regarding the custom Total and % columns in the "Financial Reporting Matrix by Profit Base" visual was already addressed earlier.
A working DAX solution along with a sample file was shared by the rohit1991 bhanu_gautam to help replicate and resolve the behavior you're targeting.
Please feel free to follow up if any additional clarification is needed.
PBIdashboardsPost Patron
The HASONEVALUE pattern in the accepted solution is correct for conditional total rows. For the % column specifically, you need to pass through blank for non-total rows:
% Column =
IF(
HASONEVALUE(DimAccounts[Description]),
BLANK(),
DIVIDE([Total Value], [Base Value])
)If the Profitbase visual keeps requiring complex DAX workarounds for standard P&L columns, it's worth comparing with Flexa Tables on AppSource variance columns (absolute + %) are built-in buttons, no HASONEVALUE patterns needed. Free trial available
