Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
francoisl
Helper I
Helper I

Conditional sums based on another measure

Hi all, 

 

Here is the data. 

francoisl_0-1701199780973.png

 

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 box.  (Sum cout) / Ventes. 

2) if the cost is 0 I do not want to compute the % and I do not want to add neither the cost or the Ventes to the yearly total. 

 

Any ideas...  Thanks

1 ACCEPTED SOLUTION

Hi, @francoisl 

 

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

vzhangti_0-1701325915840.png

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]))

vzhangti_1-1701326341624.png

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.

View solution in original post

5 REPLIES 5
vanessafvg
Super User
Super User

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.





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




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               

Hi, @francoisl 

 

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

vzhangti_0-1701325915840.png

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]))

vzhangti_1-1701326341624.png

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.

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





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




Yes Periods are column names and from a calendar tables. 

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.