How to correctly calculate totals for measures across many months when there are different condition

[dlopesc04]

So initially I created my measures for this table as such (These Measures are actually columns in a Matrix and the rows are [Category]):

Measure_Name	Value
Measure 1	Previous * 0.1
Measure 2	Previous * 0.3
Measure 3	Previous * 0.2
Measure 4	Previous * 0.1
Measure 5	Previous * 0.15
Measure 6	Previous * 0.15

Then came a requirement to change the values multiplying the Previous Measure based on selected month, So I added an if statement to every Measure:

Measure = [Previous] * IF(MAX('DateTable'[Date])<=DATE(2023,08,31), 0.2 , 01)

Where [Date] is a Year/Month slicer on my page. It worked fine for some time but then came the requirement of seeing the total of a year, PROBLEM: with the above logic when seeing the total of the year it always picks only one value for all months:

Value of Measure =  [Total Value] * 0.1

There should be a selection of values using "0.2" and another using "0.1" like so:

Value of Measure = SUMX([Total Value of Each Month] * IF( [Month Date] <=DATE(2023,08,31), 0.2 , 01) * (Month Grand Total %))

↑
↑ At least that is what I _Think_ the total value shown for these measures should be, when the period selected spans more than one month, but is there an easier way to calculate this? I feel like I'm missing something very simple.

Further info: In this case all the measures are pulling data from different tables, the matrix constructed with these measures doesn't have a corresponding existing table in my Semantic Model. Also all the tables have a slightly different structure and granularity, to make all of this work the [Previous] part can end up being very complicated, on of them even has more than 200 lines in order to accommodate all possible filtering scenarios.

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[dlopesc04]

Hello! Thank you for replying, while I can't attach a .pbix file, here are some more concrete examples and data of what I'm trying to achieve in Power BI:
Table 1 - Actual data:

M_num	Month	PCP	PROD	IMP	REP	SOB	SUCT	GLB
1	January	87,40%	91,50%	91,80%	100,00%	100,00%	45,60%	86,40%
2	February	94,80%	96,80%	97,20%	100,00%	96,50%	55,80%	90,80%
3	March	99,50%	98,80%	100,00%	99,10%	91,60%	60,70%	92,30%
4	April	99,40%	100,00%	99,20%	100,00%	98,10%	74,30%	95,70%
5	May	99,20%	99,40%	98,30%	100,00%	97,20%	80,70%	96,10%
6	June	97,90%	83,40%	98,60%	98,20%	99,30%	69,10%	89,60%
7	July	94,60%	81,00%	95,10%	84,40%	98,80%	45,70%	82,90%
8	August	99,70%	87,40%	100,00%	97,80%	99,90%	63,60%	90,50%
9	September	100,00%	85,60%	99,30%	100,00%	98,90%	78,80%	92,50%
10	October	98,60%	80,70%	99,00%	100,00%	93,20%	81,20%	90,70%
11	November	100,00%	80,70%	96,60%	100,00%	97,40%	96,10%	94,10%
12	December	100,00%	79,60%	98,30%	100,00%	94,50%	81,90%	90,90%
	Total	98,70%	91,70%	98,00%	100,00%	99,00%	63,60%	90,30%

Table 2 - Values to be multiplied:

Value	Now	Before
PCP	0,1	0,1
PROD	0,2	0,3
IMP	0,2	0,2
REP	0,1	0,1
SOB	0,2	0,15
SUCT	0,2	0,15

The "GLB" is the result of the multiplication of each other value by their weight, this weight has changed mid year, starting in September. The values in yellow are from the Power BI automatic totals, the values in blue and green are correctly calculating the "GLB" for that month.

Here's how the GLB could be based if using every weight for every year:

GLB TOTAL
L	K
Now	Before
0,8452	0,8639
0,8874	0,9081
0,9008	0,9235
0,9426	0,9564
0,9504	0,9609
0,8969	0,8961
0,8202	0,8290
0,8993	0,9050
0,9252	0,9220
0,9068	0,9003
0,9416	0,9256
0,9086	0,9000

The values in green and blue are what I wish to have be part of the total, but Power BI uses only the values in Green and Orange to calculate their total (Basically using only the most recent weights)

I wish to accomplish this in Power BI:

J	L	K
Month	GLB	CALC
January	0,072	=(L3/12)
February	0,076	=(L4/12)
March	0,077	=(L5/12)
April	0,080	=(L6/12)
May	0,080	=(L7/12)
June	0,075	=(L8/12)
July	0,069	=(L9/12)
August	0,075	=(L10/12)
September	0,077	=K11/12
October	0,076	=K12/12
November	0,078	=K13/12
December	0,076	=K14/12
FINAL GLB	91,04%	=SUM(L17:L28)

My measures end up looking something like this:

CALCULATE( [5PCP_Total]*0.1, 'DataTable'[MMM] = "jan" )/DISTINCTCOUNT('DataTable'[Month Name]) +

CALCULATE( [5PCP_Total]*0.1, 'DataTable'[MMM] = "feb" )/DISTINCTCOUNT('DataTable'[Month Name]) +

CALCULATE( [5PCP_Total]*0.1, 'DataTable'[MMM] = "mar" )/DISTINCTCOUNT('DataTable'[Month Name]) +

CALCULATE( [5PCP_Total]*0.1, 'DataTable'[MMM] = "apr" )/DISTINCTCOUNT('DataTable'[Month Name]) +

CALCULATE( [5PCP_Total]*0.1, 'DataTable'[MMM] = "may" )/DISTINCTCOUNT('DataTable'[Month Name]) +

CALCULATE( [5PCP_Total]*0.1, 'DataTable'[MMM] = "jun" )/DISTINCTCOUNT('DataTable'[Month Name]) +

CALCULATE( [5PCP_Total]*0.1, 'DataTable'[MMM] = "jul" )/DISTINCTCOUNT('DataTable'[Month Name]) +

CALCULATE( [5PCP_Total]*0.1, 'DataTable'[MMM] = "aug" )/DISTINCTCOUNT('DataTable'[Month Name]) +

CALCULATE( [5PCP_Total]*0.2, 'DataTable'[MMM] = "sep" )/DISTINCTCOUNT('DataTable'[Month Name]) +

CALCULATE( [5PCP_Total]*0.2, 'DataTable'[MMM] = "oct" )/DISTINCTCOUNT('DataTable'[Month Name]) +

CALCULATE( [5PCP_Total]*0.2, 'DataTable'[MMM] = "nov" )/DISTINCTCOUNT('DataTable'[Month Name]) +

CALCULATE( [5PCP_Total]*0.2, 'DataTable'[MMM] = "dec" )/DISTINCTCOUNT('DataTable'[Month Name])

And this is repeated for every other value.

PS. The measures in these calculation (e.g [5PCP_Total]) are not coming from a neat table like table 1 of this comment, they are all measures pulling from different tables, and when putting the final calculation in a visual it just breaks due to exceeding resource limit. I can remove the limit in the report options and see that the calculations do work, but on the service it won't do.

[lbendlin]

First you need to bring your data into usable format by unpivoting it.

Then you can use HASONEVALUE to sense where in the matrix you are.

Here is the code for the column totals.  Not sure what you expect for the row totals

[dlopesc04]

You may note that the total you are showing in your table is already present in the table I used as an example. Thats because my measures are already correctly calculating this column which you called "Total".

In my tables it's called "GLB". What I need now is the total for the year. One "GLB" value for the 12 months. 

This value highlighted is still wrong. It should be the one in orange from the following table:

Month	GLB	CALC
January	0,072	=(L3/12)
February	0,076	=(L4/12)
March	0,077	=(L5/12)
April	0,080	=(L6/12)
May	0,080	=(L7/12)
June	0,075	=(L8/12)
July	0,069	=(L9/12)
August	0,075	=(L10/12)
September	0,077	=K11/12
October	0,076	=K12/12
November	0,078	=K13/12
December	0,076	=K14/12
FINAL GLB	91,04%	=SUM(L17:L28)

[lbendlin · Accepted solution]

[dlopesc04]

That is absolutely brilliant. Thank you.