Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Subtraction Row wise Calculation

Hi Team,

 

DAX - based on UID (Unique) total sum value subtraction with each UID Category like row wise, could you please help me.

 

Note:

1) stock and sal_total both are measures value (calculated new measures)

2) Blac_Manual -> expected result (Manual Calculation 

3) Cat -> row wise subtract UID sum of total like first ex : GH-258 Stock is 14 - 5 (UID sum) = expecting result is 9

 

formul Not working : 

balc = sumx(filter(Sheet2, [UID] = earlier([UID]) ),[Stock]) - maxx(filter(Sheet2, [UID] = earlier([UID]) ),[Production])

 

 

 

Thanks,

KV's

Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi  Anonymous  ,

    According to your description, I create this data：

    Here are the steps you can follow：

    1. Create measure.

    Stock: Cat category value when uid is consistent

stock =
CALCULATE(SUM('Table'[amount]),FILTER('Table','Table'[Cat]=MAX('Table'[Cat])))
    sal_total：Sum by [cat] class UID

sal_total =
var _UID=CALCULATE(MAX('Table'[UID]),FILTER(ALL('Table'),[Cat]=MAX('Table'[Cat])))
return
CALCULATE(COUNT('Table'[UID]),FILTER(ALL('Table'),'Table'[UID]=_UID))
    Blac_Manual：Expected results

Balc_Manual = [stock]-[sal_total]

    2. Result

    You can downloaded PBIX file from here.

     

    If my answer is not what you need, can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

     

    Best Regards,

    Liu Yang

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

3 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    Anonymous , Logic is not very clear.

     

    Create a new measure like

    balc = sumx(filter(allselected(Sheet2), [UID] = max([UID]) ),[Stock]) - maxx(filter(allselected(Sheet2), [UID] = max([UID]) ),[Production])

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Hi amitchandak ,

       

      It's working DIM based total sum, but I'm expecting DIM while including Category then total volume split like below,

       

       

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi  Anonymous  ,

    According to your description, I create this data：

    Here are the steps you can follow：

    1. Create measure.

    Stock: Cat category value when uid is consistent

stock =
CALCULATE(SUM('Table'[amount]),FILTER('Table','Table'[Cat]=MAX('Table'[Cat])))
    sal_total：Sum by [cat] class UID

sal_total =
var _UID=CALCULATE(MAX('Table'[UID]),FILTER(ALL('Table'),[Cat]=MAX('Table'[Cat])))
return
CALCULATE(COUNT('Table'[UID]),FILTER(ALL('Table'),'Table'[UID]=_UID))
    Blac_Manual：Expected results

Balc_Manual = [stock]-[sal_total]

    2. Result

    You can downloaded PBIX file from here.

     

    If my answer is not what you need, can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

     

    Best Regards,

    Liu Yang

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