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.
I have the following matrix and table, the subotal is missing in the running totals where the category is empty.
Category | Sub Category | Sub Category 2 | Amount | Date |
1 | 1.1 | 1.1.1 | 1 | 01/01/20 |
1 | 1.2 | 1.2.1 | 1 | 01/02/20 |
1 | 1.3 | 1.3.1 | 1 | 01/03/20 |
1 | 1.1 | 1.1.2 | 1 | 01/04/20 |
1 | 1.2 | 1.2.2 | 1 | 01/05/20 |
1 | 1.3 | 1.3.2 | 1 | 01/06/20 |
2 | 2.1 | 2.1.1 | 1 | 01/06/20 |
2 | 2.1 | 2.1.2 | 1 | 01/05/20 |
2 | 2.2 | 2.2.2 | 1 | 01/04/20 |
2 | 2.2 | 2.2.1 | 1 | 01/03/20 |
2 | 2.3 | 2.3.1 | 1 | 01/02/20 |
I used quick measure for running total -
Solved! Go to Solution.
I think this has to do with the structure of the data, what I did was this:
ID_CAT = 'Table'[Category]&'Table'[Sub Category]&'Table'[Sub Category 2]
IDCAT = SUMMARIZE('Table';' Table'[Category];' Table'[Sub Category];' Table'[Sub Category 2];' Table'[ID_CAT])
Now create the following measure:
Total Value = CALCULATE(sum('Table'[Amount]); FILTER(ALLSELECTED(IDCAT[Category]);IDCAT[Category]<= MAX(IDCAT[Category])))
Result below and in attach PBIX file.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @mahendi ,
Do you want a sub total for every subcategory so looking at your data in January all subtotals show 1 on February first will show 1 next will show 2 and so on?
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em Português
I think this has to do with the structure of the data, what I did was this:
ID_CAT = 'Table'[Category]&'Table'[Sub Category]&'Table'[Sub Category 2]
IDCAT = SUMMARIZE('Table';' Table'[Category];' Table'[Sub Category];' Table'[Sub Category 2];' Table'[ID_CAT])
Now create the following measure:
Total Value = CALCULATE(sum('Table'[Amount]); FILTER(ALLSELECTED(IDCAT[Category]);IDCAT[Category]<= MAX(IDCAT[Category])))
Result below and in attach PBIX file.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em Português@MFelix Thank you for the suggestion but in this particular scenario the display sequence is important.
I am trying to display running total in the sub total rows. if a category is empty it should still carry the total from previous sub total as it is a running total.
Hi @mahendi,
Then the issue is with your formula you are only picking up values that are above the category you must use a sumx for the subtotals.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @mahendi ,
The calculation you have is correct based on the matrix setup and the measure.
In your measure you are picking all the values that are On or After your Maximum category value, looking at the image you have for category 1 subtotal the value 1 for category 2 there is no value so the Sub Total value for that category is blank and it cannot be 1.
Believe you are looking at the sub total at a different way, I have made some changes to the setup of the matrix and renamed the totals:
Has you can see the lines that refer to subcategory is blanks.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsCovering 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 |
---|---|
110 | |
94 | |
80 | |
67 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |