Forum Discussion

kaz5281's avatar
kaz5281
Frequent Visitor
2 years ago

Sum filtered rows with calculation in between

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

 

DateJan-2021Feb-2021
Object  
ObjA14
ObjB25
ObjD36

 

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

DateJan-2021Feb-2021
Object  
ObjA14
ObjB25
ObjD36
subtotal_obj3369
subtotal_obj_perday 1.062.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.

Need Help

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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.

     

     

     

     

  • kaz5281's avatar
    kaz5281
    Frequent Visitor
    2 years ago

    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...