Question Set Expression

[Mitch_Data]

Hi,

Problem:

Dataset has two categories: we call them Tyres and Damage.
Two categories have three subcategories each: E.g. Tyres(batch of companies 1)(batch of companies 2) (batch of comp 3), the same for damages.

Then we have Year : 2019, 2020 and 2021 and all months within these years.
Then lastly we have a  field number of days (that a vehicle is on the road).

Question:
How do I make a set expression that calculates the percentage that the 3 subcategories have regarding number of days.

So I want to know for how much % the batch of companies 1 are represented in the dataset.

However, I want it to be represented in a way that we calculate the % based on the total of the month and in the respective year, for instance 2019.

So in a formula it would look like this:

IF(COMPANY_VALUE='XXX' AND MONTH = '01' AND YEAR='2019', SUM(TOTAL OF DAYS)/sum(TOTAL if(VALUE='TYRES' AND YEAR= '2019' AND MONTH = '01', [NUMBER OF DAYS])),

This is a formula that works for january 2019, but I want it to be automatically calculated for all other months and years for each subcategory, without hard-coding a '2019'for example as year.

I know it is a bit vague, so please if you have questions feel free to reply.

[anthonyj]

Hi @Mitch_Data ,

I'm not sure how you're planning on visualising this so as an example I'll provide an option you can try. From your question it sounds like there's going to be a table that will include:
Categories, Subcategories, Month and Year as dimensions.
We can leverage these dimensions to provide your percentage. From your example you want to provide a particular "Company_Value"'s percentage of all "Company_Value" within the same month and year.

So using the above dimensions in a table you could add a measure like:
sum([Number of Days])/sum(total <Month, Year> [Number of Days])
The added dimensions will aggregate your number of days into their relevant dimensions and using the TOTAL function will aggregate across your dimensions.
The <Month, Year> will keep the aggregations accross your Categories and Subcategories (eg. Company 1 / (Company 1, 2 3)) but will separate in the months and years so it will compare company 1 as a percentage in July and Aug etc...

I hope this provides some assistance in your problem.

Thanks

Anthony

[Mitch_Data]

Hi!

I've tried your solution and it almost does what I want indeed. However, my next question is.

The picture below shows the outcome how the formula generated the percentages. You see that the % are very low. Nonetheless, when I select for example 19-1 as a filter the bars will accumulate to a 100% also for the other months respectively (see picture 2) Is there a way to always show the result of picture 2, even without selecting a filter? I want them all to add up to 100% regardless of filter for each month.

[Mitch_Data]

Any help appreciated

[anthonyj]

Hi @Mitch_Data ,

Sorry I haven't had a chance to get on in a while. If I understand you correctly you don't want the graphs to move if you choose filters for your year_month. It looks like you've created a column comprising of year & '_' & month.

You can add set analysis to prevent interactivity with that column in the filters. I believe the 'total' function will prevent the interactivity already:

sum({$<year_month=>}[Number of Days])/sum(total <year_month> [Number of Days])