Forum Discussion

francoisl's avatar
francoisl
Helper II
2 years ago
Solved

Conditional sums based on another measure

Hi all,    Here is the data.    I want to be able to compute a % of cost (cout) on sales (ventes) in two different ways: 1) within the Period (2023-P10) for exemple, straight out of the bo...
Need Help
  • v-zhangti's avatar
    v-zhangti
    2 years ago

    Hi, francoisl 

     

    I've simply modelled some data to hopefully fit your situation.

    If [Sales], [Cost], and [%] are all measures, you can try the following.

    M1 = 
Var _blankDate=CALCULATE(MAX('Table'[Date]),FILTER(ALL('Table'),[Cost]=0))
Return
IF(HASONEVALUE('Table'[Date]),[Sales],SUMX(FILTER('Table',[Date]<>_blankDate),[Sales]))
    M2 = 
Var _blankDate=CALCULATE(MAX('Table'[Date]),FILTER(ALL('Table'),[Cost]=0))
Return
IF(HASONEVALUE('Table'[Date]),[Cost],SUMX(FILTER('Table',[Date]<>_blankDate),[Cost]))
    M% = 
Var _blankDate=CALCULATE(MAX('Table'[Date]),FILTER(ALL('Table'),[Cost]=0))
Return
IF(HASONEVALUE('Table'[Date]),[%],DIVIDE([M2],[M1]))

    Is this the result you expect? Please see the attached document.

     

    Best Regards,

    Community Support Team _Charlotte

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

vanessafvg's avatar
vanessafvg
Community Champion
2 years ago

can you give an example of what you are expecting?

 

then also provide some sample data with desired outcome.  This will help to ensure your problem is fully understood.

 

you can either copy and paste text data with an example what you are expecting.

  • francoisl's avatar
    francoisl
    Helper II
    2 years ago

    Of Course. 

    Here's a table with data.  

     

    The total should only be totals based on the period where the cost <> 0. Meaning we want to ignore any periods that have a 0$ cost.  

     

    At this point it ignores only fiscal period P8.

     

    Many thanks

     

    Francois

     2023-P8  2023-P9  2023-P10  2023-P10  Total  
     SalesCost%SalesCost%SalesCost%SalesCost%SalesCost%
    Dept1100000.00%120012010.00%5007515.00%200023011.50%370042511.49%
    Dept2               
    Dept3               
    • vanessafvg's avatar
      vanessafvg
      Community Champion
      2 years ago

      is this how the table looks in power bi?  are the periods column names?  

      • francoisl's avatar
        francoisl
        Helper II
        2 years ago

        Yes Periods are column names and from a calendar tables. 

    • v-zhangti's avatar
      v-zhangti
      Community Support
      2 years ago

      Hi, francoisl 

       

      I've simply modelled some data to hopefully fit your situation.

      If [Sales], [Cost], and [%] are all measures, you can try the following.

      M1 = 
Var _blankDate=CALCULATE(MAX('Table'[Date]),FILTER(ALL('Table'),[Cost]=0))
Return
IF(HASONEVALUE('Table'[Date]),[Sales],SUMX(FILTER('Table',[Date]<>_blankDate),[Sales]))
      M2 = 
Var _blankDate=CALCULATE(MAX('Table'[Date]),FILTER(ALL('Table'),[Cost]=0))
Return
IF(HASONEVALUE('Table'[Date]),[Cost],SUMX(FILTER('Table',[Date]<>_blankDate),[Cost]))
      M% = 
Var _blankDate=CALCULATE(MAX('Table'[Date]),FILTER(ALL('Table'),[Cost]=0))
Return
IF(HASONEVALUE('Table'[Date]),[%],DIVIDE([M2],[M1]))

      Is this the result you expect? Please see the attached document.

       

      Best Regards,

      Community Support Team _Charlotte

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