Need help figuring out a formula for mutiple measurements

[AleksanderPunt]

I work at a waste management company and create statistics on weighing waste items in car scales.

• Private household customers don't weigh on car scale
• Industrial customers weigh on the automotive scale (Article Nr from 200 to 299)
• Domestic and industrial waste is mixed and weighed at the car level (Article No. 700 to 799)

I need to find the weight of household waste.

Therefore, industrial waste must be subtracted from mixed waste - for each item.

The measurement for an individual item is easy to find. Let's take a look at "Iron," which has ArticleNr "730" for mixed weighings, and "230" for industrial weighing. For this example, I will only focus on the total weight and not the YTD values.

Iron for the Home (Iron Home)

VAR Mixed Iron = CALCULATE ( SUM (Table[Weight]) ; Table[ArticleNr] - "730" )

VAR Industrial Iron = CALCULATE ( SUM (Table[Weight]) ; Table[ArticleNr] - "230" )

Return

Mixed Iron - Industrial Iron

Now, let's go one deeper (as DJ Hanzel says):

I need to find values for the year to date (YTD) and YTD last year (LY) for each household waste item.

I've managed to create measures for YTD and YTD LY, but only generic measures that only work on current rows within the data table.

That is, I can find out the numbers of waste items that are weighed - but not for household waste, you need to have a subtraction (mixture - industrial) to calculate the weight.

Mixed waste of industrial and household waste

ArticleNr	ArticleName	Weight YTD LY	Weight YTD
730	Iron	500	300
751	Paper	550	450
766	Plastics	600	200

Industrial waste

ArticleNr	ArticleName	Weight YTD LY	Weight YTD
230	Iron	250	100
251	Paper	50	40
266	Plastics	150	150

This is what I can show now, with two measures inside an array table.

I want to show household waste in a similar way.

The formulas I have used for my current measurements:

Weight YTD ?

CALCULATE ( SUM ( Table[Weight] ) ; DATESYTD ( DateTable[Date] ) )

Weight YTD LY ?

VAR StartDate ( DATE ( Year ( TODAY() ) -1 ; 1 ; 1 ) )

VAR EndDate ( DATE ( Year ( TODAY() ) -1 ; MONTH ( TODAY() ) ; DAY ( TODAY() ) ) )
Return

CALCULATE ( SUM ( Table[Weight] );

DATESBETWEEN ( DateTable[Date] ;

StartDate ; Enddate ) )

Is it possible to create a measure that calculates the YTD weight - for each household waste item?

And a measure for YTD LY - for every household waste item?

I have already created an assigned entry-table, indicating which 200-series-ArticleNr to subtract from the 700-series-ArticleNr.

I have also created a calculated column that classifies a row as "Mix", "Industrial" or "Blank" - based on the first number in the ArticleNr --> IF ( LEFT ( Table[ArticleNr]) ; 1 ) - "2" ; "Industrial" ; ... )

But I don't know if there's a need for this.

[AllisonKennedy]

I agree with @v-easonf-msft , what does your raw data look like?

The Last Year measure is actually not needed if you just put the YTD weight in a table with Year in columns.

As for your question, measures are dynamic and follow the context of the report visualizations, so if you simply create a MEASURE:

Household Waste = [Mixed Waste]-[Industrial Waste] 

that should do the trick. 

When you put this measure in a visualization by material, then you'll have the right answer. 

Your problem is that industrial iron has a different number to household iron, thus why I need to know what your raw data looks like (relationships view please) to be more helpful. If you have a nice data model though, you can pull the material labels from a DimMaterial table and the measures should talk to each other. 

Has this post solved your problem? Please mark it as a solution so that others can find it quickly and to let the community know your problem has been solved. 

If you found this post helpful, please give Kudos.

I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query. 

https://sites.google.com/site/allisonkennedycv

[AleksanderPunt]

Well, I can try to put togheter a sample-file with irrrelevant columns and articles trimmed - as well as limiting the time period.

All the data is in one datatable. Industrial waste doesn't have corresponding number-series as what to subtract from the mixed series. So I suppose the logic need to be entered in a manually entered table, where the column for article numbers is mapped to the original data table.

Will this work?

Column 1 - ArticleNr: Listing all article numbers

Column 2 - Article: Corresponding names across both mixed and industrial articles

Column 3 - Type: Labeling the article as Industrial, Mixed or Other

ArticleNr	Article	Type
205	Food	Industrial
276	Wood	Industrial
705	Food	Mixed

So, if I'm using your measure as an example:

HH Waste = [Mixed Waste] - [Industrial Waste]

Will it be sufficient to have a common article name for industrial and mixed waste (like I did with Food in the table above), and setting this column (article") as the row header and your measure as value?

I tried your tip on just having YTD measure and putting year in columns.

This worked in a matrix, but lacks some benefits of a regular table.

1. In the regular table I inserted an article icon/logo, mapped through another datatable and collecting the images online. In the next column are the article names. In matrix it's only possible to have one dedicated row header column. Drill down is possible, but not something I wish for.

2. Percentage diff from year to year is not possible. It only displays weight per year.

3. The matrix takes into account the whole year, so I need to put in a filter for dates in order to limit the selection to a time period.

I found that by using a date-filter which can filter by day-number-of-year , I get to filter YTD or other time intervals across multiple years and not only one year.

So, I set the slicer to start on day 1 (1st of January) and end on day 127 (6th of May).

There might be a smoother solution for this, but I haven't found a date slicer which doesn't filter on a specific time interval, meaning year is also included.

DayofYear = DATEDIFF ( DATE ( YEAR ( DateTable[Date] ) ; 1 ; 1 ) ; DateTable[Date] ; DAY )

^By having two measures for YTD and YTD LY, I don't need to manually configure the filter in order to see current YTD values. This is especially nice if the report is published and updated daily. But hey, if there was a way to automatically set the time interval filter to today's date for the second date, it would automatically update the matrix table 🙂

[AllisonKennedy · Accepted solution]

@AleksanderPunt  Yes, that should work provided everything is in one source data table. Just use the [Article] column as row header and my suggested measure as your value. What that will do is sum all the industrial waste for that article for the selected time period, and sum all the mixed waste for that article for the selected time period and subtract them. 

As for your Date slicer problem, try updating your Peso YTD LY measure to be something more like: 

Peso YTD LY = CALCULATE([PesoYTD], DATEADD(DimDate[Date], -1, YEAR)) 

or similar.

Note for that to work you will need to create a DimDate table for your data model. Date tables are very important for getting accurate results in Time Intelligence (ie YTD) measures.

1. Here's one way to create a DATE table: https://www.mattmasson.com/2014/02/creating-a-date-dimension-with-a-power-query-script/
2. Or you can use CALENDARAUTO() to create the Date table using DAX (visuals may be slower to load in this case)

Once you have the DimDate table, you can use the Date from that table to filter for this year and the Peso YTD LY will still display for last year because we've used DATEADD to shift the selection of dates in the filter to last year's dates. 

Has this post solved your problem? Please mark it as a solution so that others can find it quickly and to let the community know your problem has been solved. 

If you found this post helpful, please give Kudos.

I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query. 

https://sites.google.com/site/allisonkennedycv

[AleksanderPunt]

The eyeopener here was to map all codes as either "Mixed Waste" or "Industrial Waste", and then subtract the sum of "Industrial Waste" from the sum of "Mixed Waste" in a measure. Thanks a bunch, AllisonKennedy!!

I created a new table and manually entered all the article numbers in a column and labeled each row in multiple columns for future grouping-convenience. The main columns making the measure possible were ArticleNr, Article and Type. Afterwards I mapped the ArticleNr-column from this table to the main table ArticleNr-column.

Here's the final measure:

HH Waste =

VAR Mix = CALCULATE ( SUM ( Table[Weight] ) ; CodeMapping[Type] = "Mix" )

VAR Industrial = CALCULATE ( SUM ( Table[Weight] ) ; CodeMapping[Type] = "Industrial" )

RETURN

Mix - Industrial

Then I just put Article as row header, year as column header and the HH Waste measure as value 🙂

BTW, it seems like the html-formatting had some hickups during my initial post. Some words were exchanged with spanish ones, haha. I have edited it now. I never wrote Peso or anything like that ;D

[v-easonf-msft]

Hi , @AleksanderPunt 

Are all the data " Mixed waste of industrial and household waste" and " Industrial waste" in the same table?

Can you share me some sample data /.pbix file to test ?

Best Regards,
Community Support Team _ Eason