Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello,
I created a measure to essentially replace values for a category in the visual (08&04 Reforecast) with values from another category (Actuals) if the year and month was less than or equal to 202207 without impacting the other category values. My measure seems to be working well as you can see in the screenshot below but my subtotals are displaying values from the Actuals instead of adding up the values within rows.
Here is my DAX:
Measure =
SWITCH(
TRUE(),
MAX('PBIpull'[Type])="08&04 Reforecast" && MIN('PBIpull'[Year Month Code])>=202207,
CALCULATE(SUM('PBIpull'[Count]),'PBIpull'[Type]="08&04 Reforecast"),
MAX('PBIpull'[Type])="08&04 Reforecast" && MIN('PBIpull'[Year Month Code])<=202206,
CALCULATE(SUM('PBIpull'[Count]),'PBIpull'[Type]="Actuals"),
CALCULATE(SUM('PBIpull'[Count]))
)
My sample file: https://drive.google.com/file/d/17VKscN4qDQm1YS1G55nX-EzUioMjBTKq/view?usp=sharing
My PBI file: https://drive.google.com/file/d/1V_SpZW_HejtOe6Qd5v8f-RSYObEeDkss/view?usp=sharing
I'd appreciate any help with this. Thank you.
Solved! Go to Solution.
Measure =
var a = SUMMARIZE(PBIpull,[Area],[Sub-Area],[Year Month Code],[Type])
var b = ADDCOLUMNS(a,"sm",if([Type]="08&04 Reforecast" && [Year Month Code]<=202206, CALCULATE(SUM('PBIpull'[Count]),'PBIpull'[Type]="Actuals"),calculate(sum(PBIpull[Count]))))
return sumx(b,[sm])
I also disconnected your Calendar table and created a calculated column in the PBIpull table
Month Number = value(right([Year Month Code],2))
Try to avoid M:M bidirectional relationships.
Thanks @lbendlin , I've attached my sample file above. My expected outcome is just that I need my matrix subtotals for the 08&04 Reforecast Measure to reflect the sum of the values in each year below which I'm not seeing currently as you can see in the screenshot above. Appreciate your help thank you.
Measure =
var a = SUMMARIZE(PBIpull,[Area],[Sub-Area],[Year Month Code],[Type])
var b = ADDCOLUMNS(a,"sm",if([Type]="08&04 Reforecast" && [Year Month Code]<=202206, CALCULATE(SUM('PBIpull'[Count]),'PBIpull'[Type]="Actuals"),calculate(sum(PBIpull[Count]))))
return sumx(b,[sm])
I also disconnected your Calendar table and created a calculated column in the PBIpull table
Month Number = value(right([Year Month Code],2))
Try to avoid M:M bidirectional relationships.
That works thank you! Can I apply the same formula if the values that I need to change are from a measure instead of a column?
You need to try that out. Usually the recommendation is to avoid nesting measures when possible.
Please provide sanitized sample data that fully covers your issue. I can only help you with meaningful sample data.
Please paste the data into a table in your post or use one of the file services like OneDrive or Google Drive. Screenshots of your source data are not useful.
Please show the expected outcome based on the sample data you provided. Screenshots of the expected outcome are ok.
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...
https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523
User | Count |
---|---|
103 | |
88 | |
78 | |
71 | |
69 |
User | Count |
---|---|
113 | |
99 | |
97 | |
72 | |
68 |