Forum Discussion
Matrix Totals & Visual issue
Hi all,
Dealing with the following 2 issues (Attached a PBI example file).
- The Visual (Sales Actuals vs Theorical) on page "Insights option". I am not able to properly visualize fields/measures "Theorical sales " and "Sales ACT vs Theorical"
- On page (Plant 1), Not able to have the total in the Matrix for the fields/measures: "Actuals Consumption" and "Theorical Sales".
https://drive.google.com/file/d/1nS1GFH3AEwInsue-yF1ORiepCEOe2Ky5/view?usp=drive_link
Thank you in advance,
Hi Msampedro ,
Thank you for reaching out to the Microsoft Fabric Community Forum.
After thoroughly reviewing the details you provided, I was able to reproduce the scenario, and it worked on my end. Fixed the measure for now.
I am including .pbix file for your better understanding, please have a look into it:
Thank you.
10 Replies
The issue is comming from your mesasure:
Theorical Sales"Theorical Sales = [Unique Qty]*SELECTEDVALUE('2. Renault Price List'[Price DAP €/kg])This measure needs one and only one value return from SelectedValue, if many values are returned, the measure will be blank
If you add a table with this field and select a value, you will have value in your matrix:
For the issue with Consumption actual vs Theorical, you used a conditional formatting which is hidding your value
If I removed it, I get some values
Do not hesistate to ask if you need more support to fix these issues
Thanks Cookistador
Regarding the measure "Actual consumption" - Do you know how I can adapt the formula to see the totals?
Also - Do you know how to correctly visualize the below 3 measures?
Many thanks,
Miguel
hI Msampedro
Use IN...VALUES instead of = SELECTEDVALUE
Unique Qty = VAR _Raw = VALUES ('Material Mapping'[GMC]) RETURN IF( ISINSCOPE('Material Mapping'[Billing GMC]) || ISINSCOPE('Material Mapping'[Billing GMC description]), BLANK(), -- Hide duplicates for each Billing GMC CALCULATE( SUM('Qty ACT QP1'[Quantity_in_Entry_Unit]), 'Qty ACT QP1'[GMC] IN _Raw ) )There’s already a relationship between your fact and dimension tables, so why use a measure that just filters based on slicer selections? The relationship should handle that filtering automatically when you pick something in the slicer.
Hi Msampedro ,
Thank you for reaching out to the Microsoft Fabric Community Forum.
After thoroughly reviewing the details you provided, I was able to reproduce the scenario, and it worked on my end. Fixed the measure for now.
I am including .pbix file for your better understanding, please have a look into it:
Thank you.
Hi Msampedro
Ensure both measures return numeric values in all contexts.
For the difference measure (Sales ACT vs Theorical), define as a proper measure:
Sales ACT vs Theorical =
[Actual Sales] - [Theorical Sales]Use a combo chart:
X-axis = Date or Category
Column = Actual Sales
Line = Theorical Sales or Sales ACT vs Theorical
If still not visible:Verify the data type in the model is numeric (Decimal Number / Whole Number)
Remove filters temporarily to check if slicers are hiding data
1️⃣ Adapt “Actual Consumption” to show totals in a Matrix
The problem is usually that the measure works per row, but Power BI doesn’t automatically sum it at the total level. The solution is to detect whether you are at the row level or total level using HASONEVALUE() (or ISINSCOPE()), then calculate accordingly.
Pattern for totals:
Actual Consumption Total =
IF(
HASONEVALUE('Table'[Plant]), -- Or the field used for rows
[Actual Consumption], -- Your original row-level calculation
SUMX(
VALUES('Table'[Plant]), -- Iterate over all plants
[Actual Consumption] -- Sum the original measure
)
)Alternative using ISINSCOPE (modern approach):
Actual Consumption Total =
IF(
ISINSCOPE('Table'[Plant]),
[Actual Consumption],
SUMX(
ALL('Table'[Plant]),
[Actual Consumption]
)
)✅ This ensures the Matrix totals display correctly, summing across all plants or categories.
---2️⃣ Visualizing 3 measures correctly
Assuming your 3 measures are:
1. Actual Consumption
2. Theorical Sales
3. Sales ACT vs TheoricalStep 1: Choose the right chart type
Measure Recommended Visual Notes
Actual Consumption Column chart Displays absolute values per category (Plant, Date, etc.)
Theorical Sales Line or Column Use a combo chart with Actual Consumption as column, Theorical as line
Sales ACT vs Theorical Line or Bar (secondary axis optional) Can be plotted as difference line on the same combo chart---
Step 2: Use a Combo Chart for all 3 measures
1. X-axis: Category (Plant, Month, or Date)
2. Column values: Actual Consumption + Theorical Sales (stacked or side-by-side)
3. Line value: Sales ACT vs Theorical (difference measure)
4. Y-axis: You may use dual axis if scales differ
5. Formatting:Show data labels on columns
Use color coding: Blue = Actual, Grey = Theorical, Red = Difference
---
Step 3: Test measures individually
Always validate each measure using a Card visual.
Ensure all measures return numeric values, not blanks.
Once confirmed, they’ll render correctly in the combo chart.
---
Bonus Tip for Matrix Totals
If you also need Theorical Sales totals in the Matrix:
Theorical Sales Total =
IF(
ISINSCOPE('Table'[Plant]),
[Theorical Sales],
SUMX(
ALL('Table'[Plant]),
[Theorical Sales]
)
)Same logic applies to any calculated measure.
This pattern fixes totals for measures that are row-level calculations or calculated differences.
Thanks Nabha-Ahmed
Regarding the visual, I believe the issue is the "Theorical Sales" measure, as it seems not capable to be visualized. At the below capture you can see it is blank.
Theorical Sales = [Unique Qty]*SELECTEDVALUE('2. Renault Price List'[Price DAP €/kg])Let me know if any idea to solve it.
Thanks a lot!
Miguel
Hi Msampedro ,
I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions.
Thank you.
