topic Re: DAX required to provide percentage of a latest dated value for an item based on its group label in DAX Commands and Tips

DAX required to provide percentage of a latest dated value for an item based on its group label

julesdude — Fri, 09 Sep 2022 10:04:42 GMT

Hi all

I have the following table in my data model, made simple here:

Table1:

Item Value

Item Ref	Item Value	Value Date	Supplier Ref
1	20	13-04-2021	Name1
1	15	15-02-2022	Name1
1	25	15-07-2019	Name1
2	45	25-04-2019	Name1
2	40	01-02-2020	Name1
2	30	15-09-2021	Name1
3	22	29-10-2020	Name2
3	21	13-09-2019	Name2

In my report, I want to use DAX to display a table which takes only the latest Value for each Item Ref (using the latest Value Date) and then gives the percentage of it's value against all the other items in the same Supplier Ref group.

From the above example, I'd expect that Item Ref 1 would qualify 15 (as that's the latest date provided for the value), and Item Ref 2, 30 (latest date value), and provide the percentage of those two for that group as below:

Item Ref	Item Percentage
1	33%
2	66%
3	100%

I already have the DAX to work out the latest value for a field:

ValueLastDate = Calculate(SUM(Item_Value[Item Value]), LASTDATE(Item_Value[Value Date]))

I was trying to adapt the DAX below to fit my example but couldn't get it working and not sure if it's the right approach as I need to encompass the ValueLastDate logic above.

Percent = DIVIDE ( SUMX ( 'Item_Value, 'Item_Value'[Value] ), CALCULATE ( SUM ( 'Item_Value'[Value] ), ALLEXCEPT ( 'Item_Value', 'Item_Value'[Supplier Ref] ) ) )

Grateful for any help please.

Re: DAX required to provide percentage of a latest dated value for an item based on its group label

Jihwan_Kim — Fri, 09 Sep 2022 13:22:58 GMT

Hi,

Please check the below picture and the attached pbix file.

It is for creating a new table.

New Table = VAR _maxdatetable = GROUPBY ( Item_Value, Item_Value[Item Ref], "@maxdate", MAXX ( CURRENTGROUP (), Item_Value[Value Date] ) ) VAR _maxdatevaluetable = CALCULATETABLE ( Item_Value, TREATAS ( _maxdatetable, Item_Value[Item Ref], Item_Value[Value Date] ) ) VAR _addsuppliervaluetotal = ADDCOLUMNS ( _maxdatevaluetable, "@suppliertotal", SUMX ( FILTER ( _maxdatevaluetable, Item_Value[Supplier Ref] = EARLIER ( Item_Value[Supplier Ref] ) ), Item_Value[Item Value] ) ) RETURN SUMMARIZE ( ADDCOLUMNS ( _addsuppliervaluetotal, "@ratio", DIVIDE ( Item_Value[Item Value], [@suppliertotal] ) ), Item_Value[Item Ref], [@ratio] )

Re: DAX required to provide percentage of a latest dated value for an item based on its group label

johnt75 — Fri, 09 Sep 2022 13:50:43 GMT

You can create a couple of measures like

Latest Value = IF( HASONEVALUE('Table'[Item Ref] ), var currentRef = SELECTEDVALUE('Table'[Item Ref]) var latestVal = SELECTCOLUMNS( CALCULATETABLE( TOPN( 1, 'Table', 'Table'[Value Date]), ALLEXCEPT('Table', 'Table'[Item Ref]) ), "@val", 'Table'[Item Value] ) return latestVal ) Latest val % = IF( HASONEVALUE('Table'[Item Ref]), var latestVal = [Latest Value] var totalVal = CALCULATE( SUMX( VALUES('Table'[Item Ref] ), [Latest Value]), ALLEXCEPT('Table', 'Table'[Supplier Ref]) ) return DIVIDE( latestVal, totalVal ) )

Re: DAX required to provide percentage of a latest dated value for an item based on its group label

julesdude — Fri, 09 Sep 2022 13:52:17 GMT

Jihwan_Kim thanks so much - great code which I will look to use. I don't think I can use this though as a solution on this occasion because I have other columns already in my report table (I just simplified my above example, but there are many other columns already).

Re: DAX required to provide percentage of a latest dated value for an item based on its group label

julesdude — Fri, 09 Sep 2022 14:20:22 GMT

Thanks johnt75 -

I applied this but I'm getting the following error:

Re: DAX required to provide percentage of a latest dated value for an item based on its group label

johnt75 — Fri, 09 Sep 2022 14:24:55 GMT

Looks like you have multiple entries for the same Item Ref with the same value date. If you have a unique column in the table, like an ID, you can add that as an additional sort column to the TOPN. If you don't have one then you can use Power Query to create an index column, and use that. The code would then look like

Re: DAX required to provide percentage of a latest dated value for an item based on its group label

julesdude — Fri, 09 Sep 2022 14:29:34 GMT

Absolutely right johnt75

I'll use M to put in an index column and try it out and report back. Thanks.

Re: DAX required to provide percentage of a latest dated value for an item based on its group label

julesdude — Sun, 11 Sep 2022 23:30:07 GMT

Hi johnt75

Thanks for getting back Friday. I had time to apply and create the Index column in PQ (just named 'Index').

Unfortunately the results are not giving me what I want. Dragging the measure Latest Val % to the table viz on the report gives me a column with 0% for each row. Dragging the Latest Value measure brings me the same value applied to each row - it's not zero but it's the same value all the way down.

One fortunate thing here is that this table in the data model has no reference to any other table in the data model - all data is aggregated from this one table that has been imported in.

You may know what the issue is, but perhaps I should explain again my scenario:

Here is my table in the data model:

Logic needs to be:

- Take each Supplier Ref group at a time ('Supplier1', 'Supplier2' etc.) exploring the Item Refs in the collection

- Get the LAST Value Date for each Item Ref (highlighted green above)

- Sum each LATEST Item Ref value (green) to get the total for this Supplier Ref collection

- Divide the individual Item Value (highlighted green) against the above sum total of all last dated Item ref values (all the shaded green values for the supplier group) to get the percentage

- Repeat for the next supplier Ref - 'Supplier2' etc etc. to get the % of Item Refs in that collection and so forth.

The column should basically then provide the result in Column Result column in the above example.

In my report, the table displays like this

Item Ref %

1 12%

2 25%

3 18%

4 45%

52 68%

6 32%

Re: DAX required to provide percentage of a latest dated value for an item based on its group label

johnt75 — Mon, 12 Sep 2022 09:01:20 GMT

Can you share a PBIX file, or some sample data which can be copied / pasted ?

Re: DAX required to provide percentage of a latest dated value for an item based on its group label

julesdude — Mon, 12 Sep 2022 09:05:03 GMT

Hi johnt75

Thanks. Sure, you could use the sample data in my example? That ok?

Pasted below.

Item Ref	Item Value	Value Date	Supplier Ref	Index
1	20	13/04/2021	Supplier1	1
1	15	15/02/2022	Supplier1	2
1	25	15/07/2019	Supplier1	3
2	45	25/04/2019	Supplier1	4
2	40	01/02/2020	Supplier1	5
2	30	15/09/2021	Supplier1	6
3	22	29/10/2020	Supplier1	7
3	21	13/09/2019	Supplier1	8
4	55	21/10/2022	Supplier1	9
52	73	14/07/2021	Supplier2	10
52	44	15/06/2020	Supplier2	11
52	85	02/06/2019	Supplier2	12
52	36	05/07/2018	Supplier2	13
52	75	04/06/2017	Supplier2	14
6	34	14/07/2021	Supplier2	15
6	42	21/02/2020	Supplier2	16
6	67	29/01/2019	Supplier2	17

Re: DAX required to provide percentage of a latest dated value for an item based on its group label

johnt75 — Mon, 12 Sep 2022 09:14:59 GMT

The code I gave is working when I try it against this sample data

Latest Value = IF ( HASONEVALUE ( 'Table'[Item Ref] ), VAR currentRef = SELECTEDVALUE ( 'Table'[Item Ref] ) VAR latestVal = SELECTCOLUMNS ( CALCULATETABLE ( TOPN ( 1, 'Table', 'Table'[Value Date], DESC, 'Table'[Index], DESC ), ALLEXCEPT ( 'Table', 'Table'[Item Ref] ) ), "@val", 'Table'[Item Value] ) RETURN latestVal ) Latest val % = IF( HASONEVALUE('Table'[Item Ref]), var latestVal = [Latest Value] var totalVal = CALCULATE( SUMX( VALUES('Table'[Item Ref] ), [Latest Value]), ALLEXCEPT('Table', 'Table'[Supplier Ref]) ) return DIVIDE( latestVal, totalVal ) )

Re: DAX required to provide percentage of a latest dated value for an item based on its group label

julesdude — Mon, 12 Sep 2022 12:53:14 GMT

Hi johnt75

That's really strange. The only thing I can think of - I did simplify it in my example, but in my report table I am linking it to another table in the data model with details about the individual items - name, country of origin etc. just to bring them in to the table. It's linked in the data model like so:

Item Details table in model:

Item Ref	Item Name	Country
1	Name1	UK
2	Name2	FR
3	Name3	FR
4	Name4	DE
6	Name6	DE
52	Name52	UK

The resulting table in the report would bring in some details from this other table, ideally looking like this:

But I wouldn't of thought this would upset any of the DAX you've provided.

Do any of the columns need to be sorted in an order in Power Query at the transformation stage before the DAX kicks in?

Re: DAX required to provide percentage of a latest dated value for an item based on its group label

johnt75 — Mon, 12 Sep 2022 13:03:16 GMT

In the Latest Val measure, the ALLEXCEPT needs to refer to the column you are using in the visual. You can use the column from either table, doesn't matter, but they need to match

Re: DAX required to provide percentage of a latest dated value for an item based on its group label

julesdude — Mon, 12 Sep 2022 16:15:40 GMT

Hi johnt75

Thanks, that has certainly helped!! The Latest Value measure now provides the latest value for the Item Ref, so that is working.

For the Latest Val % it is also working too - I needed to set the format to percentage.

However, whilst I am getting the percentages, the ones displayed for an Item Ref in Supplier Ref collection don't all add to 100%. I do have a couple of report-wide filters applied to the visual, will the DAX above honour or disregard any existing filters used?

Re: DAX required to provide percentage of a latest dated value for an item based on its group label

johnt75 — Mon, 12 Sep 2022 16:22:33 GMT

It would honour any filters applied to other tables, which could pass via relationships to the Item tables. If you wanted to remove those filters as well you could add ALL('Table1') into the CALCULATE statement, you'd need to add one clause for each table you want to clear the filters on.

Re: DAX required to provide percentage of a latest dated value for an item based on its group label

julesdude — Tue, 13 Sep 2022 00:40:45 GMT

Hi johnt75

Thanks again for helping me with this.

RIght.....with a bit more pulling apart this evening, it looks like it is indeed related to the report-wide filters I'm applying.

Essentially, there are two report-wide filters, but I think number 1 is the main culprit:

#1 - filters out any Item Ref in the Item Details Table (left) that is marked 'Sold' in a column I didn't illustrate originally called Custom1.

#2 = filters out any Item Ref that has a category label of 'Sale' in a column I didn't include in the example but is part of a table not really referenced here.

It takes out the odd Item Ref with the 'Sold' label in Custom, and if I remove this filter from the filter pane, I get all the Item Refs of the collection that total 100% if added together.

What am I missing here? Is there a way I can honour the filter or allow the filter to be report-wide but for the column to calculate percentages to treat the collection as all except those that are labelled Sold in the Custom column? That way the Item Ref percentages that are listed in the resulting table all add to 100%

Re: DAX required to provide percentage of a latest dated value for an item based on its group label

johnt75 — Tue, 13 Sep 2022 08:28:44 GMT

If you're using the Item Ref from the Item Details table in your visual, and so using that in the ALLEXCEPT in the % calculation, you could add the Custom column to the ALLEXCEPT. If you're using the other version of Item Ref you could just add another ALLEXCEPT on the Item Details table

Re: DAX required to provide percentage of a latest dated value for an item based on its group label

julesdude — Tue, 13 Sep 2022 08:55:15 GMT

Hi johnt75

Would it be against the % measure? The percentages were still being calculated encompassing the Sold Item Refs in the Custom column.

Latest val % = IF( HASONEVALUE('Table'[Item Ref]), var latestVal = [Latest Value] var totalVal = CALCULATE( SUMX( VALUES('Table'[Item Ref] ), [Latest Value]), ALLEXCEPT('Table', 'Table'[Supplier Ref]), ALLEXCEPT('Table', 'Table'[Custom]) ) return DIVIDE( latestVal, totalVal ) )

Item Ref Table:

Item Ref	Item Name	Country	Custom1
1	Name1	UK
2	Name2	FR
3	Name3	FR	Sold
4	Name4	DE
6	Name6	DE
52	Name52	UK

Re: DAX required to provide percentage of a latest dated value for an item based on its group label

johnt75 — Tue, 13 Sep 2022 08:58:37 GMT

That's right.

If both columns are on the same table you can combine the 2 ALLEXCEPTs into 1, e.g.

Latest val % = IF( HASONEVALUE('Table'[Item Ref]), var latestVal = [Latest Value] var totalVal = CALCULATE( SUMX( VALUES('Table'[Item Ref] ), [Latest Value]), ALLEXCEPT('Table', 'Table'[Supplier Ref], 'Table'[Custom]) ) return DIVIDE( latestVal, totalVal ) )

Re: DAX required to provide percentage of a latest dated value for an item based on its group label

julesdude — Wed, 14 Sep 2022 09:55:48 GMT

johnt75 Hi

Thanks again for all your help here. I began testing with the script above and realised that in the above code I would need two ALLEXCEPT commands as it might need to negate filters imposed on other tables that might themselves be filtering out the Item Refs required.

In the end I could see this getting messy.

As a solution (appreciate may not be the best way), I looked again at whether I actually need these filters applied at report-wide level. The answer was no because I do not need to offer an ability for the user to switch these on or off. For this report, it will always be in one state and the filter will never be removed.
Therefore I looked again at the transform stage of the data and decided to merge queries to other tables joined by Item Ref - bringing across from the source table only the column where there is a label of sold or not. Then the final step to just filter on all rows that don't contain Sold so that we get datasets only with the relevant data.

This allows your original DAX solution to work. The percentages were split based on the value of all those in the collection - and none were in the collection that shouldn't be there and needed fitlering out.