Sum filtered rows with calculation in between

[kaz5281]

I have the following data in a matrix table, where Cups Phones and Fans are filtered from other objects (not displayed)

Date	Jan-2021	Feb-2021
Object
ObjA	1	4
ObjB	2	5
ObjD	3	6

I'd like to create a subtoal_obj where I have ObjA+ObjB+ObjD*10, then do a subtotal_obj_perday where I take subtotal_obj and divide it by the number of dates in that month-yr. So it'd create a visual that would looks like this

Date	Jan-2021	Feb-2021
Object
ObjA	1	4
ObjB	2	5
ObjD	3	6
subtotal_obj	33	69
subtotal_obj_perday	1.06	2.46

I was able to do this in a visual via 3 separate tables by creating a new conditional column in my original table where If Object=ObjA or ObjB i'd leave it alone, if =ObjD -> ObjD*10, then adding them up but I'm not sure how to merge the visuals. 

to get the obj_perday (in a 3rd table) I had another conditional column of [object]/DAY(table1[Date]) but same problem - how to union all 3 tables. I think this is a more complicated way of doing it though. 

Thanks in advance.

[kaz5281]

Thank you for your quick reply. I'm able to get those values, maybe the problem I'm having is more formatting than calculation? This is the desired output in a single visual / table. Is this possible? I'm currently able to get measure3_total in a separate table, and measure2_total in a separate table but I'm unsure of how to merge it with the value table to make one table

I've been poking around more - I think the solution may be something along the lines of what was done here (https://community.fabric.microsoft.com/t5/Desktop/Create-calculated-row/td-p/440613) but I don't entirely understand that solution yet. Still playing with it...

[v-rongtiep-msft]

Hi @kaz5281 ,

I have created a simple sample, please refer to it to see if it helps you.

Create measures.

value_m = IF(MAX('Table'[Object])="ObjD",MAX('Table'[Value])*10,MAX('Table'[Value]))

Measure = DAY(EOMONTH(MAX('Table'[date]),0))

Measure 2 = 
var _b = SUMMARIZE('Table','Table'[Object],"aaa",[value_m])
return
IF(HASONEVALUE('Table'[Object]),[value_m],SUMX(_b,[aaa])/[Measure])

Measure 3 = 
var _b = SUMMARIZE('Table','Table'[Object],"aaa",[value_m])
return
IF(HASONEVALUE('Table'[Object]),[value_m],SUMX(_b,[aaa]))

How to Get Your Question Answered Quickly - Microsoft Fabric Community

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards
Community Support Team _ Rongtie

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.