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.
Hi,
i have created a matrix which adds or removes columns based on slicers.
Where my slicers contains all dimension column names (Like Product name,Department) and Measure column names(Sales_Amt,Sales_Qty) together (i got it by unpivoting all columns in a table from edit Query).
Here is a screenshot of my report page.
Issue with this approach is
1. It doesn't give me correct GrandTotals . It might be because matrix only allows first or last values in mycase. How to get correct grand totals?
2.Export to excel shows pivot data. (which is not helpful).
This how my pivoted table looks like.
Are they any alternatives as how this can work??
Solved! Go to Solution.
Hi @Anonymous,
You can try to use following measure to replace 'value' field who used in matrix visual.
M1 = VAR currAttr = SELECTEDVALUE ( 'Dynamic Table'[Attribute] ) RETURN IF ( ISINSCOPE ( 'Dynamic Table'[Employee ID] ), FIRSTNONBLANK ( 'Dynamic Table'[Value], [Value] ), IF ( currAttr IN { "Sales_Amt", "Sales_Cost", "Sales_Qty" }, SUMX ( SUMMARIZE ( FILTER ( ALLSELECTED ( 'Dynamic Table' ), [Attribute] = currAttr ), [Employee ID], [Attribute], "Firstvalue", FIRSTNONBLANK ( 'Dynamic Table'[Value], [Value] ) ), VALUE ( [Firstvalue] ) ) ) )
Regards,
Xiaoxin Sheng
Hi @Anonymous,
If you can please share a pbix file for test, it is hard to coding formula from your snapshot.(I'm not so clear your data structure)
In addition, if you mean you want to display all item on total level, you can try to write measure with CONCATENATE functions to merge items to one by specific separator.
Measure 2 = CALCULATE ( CONCATENATEX ( VALUES ( Table[Value] ), [Value], "," ), ALLSELECTED ( Table ), VALUES ( Table[Employee ID] ), VALUES ( Table[Attribute] ) )
Regards,
Xiaoxin Sheng
Hi Xiaoxin Sheng,
Here's the link for pbix file.
https://drive.google.com/drive/folders/1W-jjRhthTOH9_V9CGg5c9BS3kwLJ6kPD?usp=sharing
Refer to page1.
Also, i want to show correct Totals for each column.
In below case,
1.Total for salescost displays first value rather than sum of SalesCost.
2. Do not show totals for Dimension Columns.
Regards,
Divya G.
Hi @Anonymous,
You can try to use following measure to replace 'value' field who used in matrix visual.
M1 = VAR currAttr = SELECTEDVALUE ( 'Dynamic Table'[Attribute] ) RETURN IF ( ISINSCOPE ( 'Dynamic Table'[Employee ID] ), FIRSTNONBLANK ( 'Dynamic Table'[Value], [Value] ), IF ( currAttr IN { "Sales_Amt", "Sales_Cost", "Sales_Qty" }, SUMX ( SUMMARIZE ( FILTER ( ALLSELECTED ( 'Dynamic Table' ), [Attribute] = currAttr ), [Employee ID], [Attribute], "Firstvalue", FIRSTNONBLANK ( 'Dynamic Table'[Value], [Value] ) ), VALUE ( [Firstvalue] ) ) ) )
Regards,
Xiaoxin Sheng
Hi Xiaoxin Sheng,
Thank you so much for the solution.
Also, export to excel gives a pivot table as underlying data is a pivot table but it is not useful to analyze data.
Is their any workaround to show same as frontend table in a tabular format ?.
Regards,
Divya G
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 |
---|---|
118 | |
104 | |
77 | |
73 | |
52 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |