topic Re: Dax - division of different but related items in DAX Commands and Tips

Dax - division of different but related items

MojoJojo15 — Thu, 10 Apr 2025 18:30:28 GMT

Hi,

I am hoping to get some help with DAX. I feel like the answer should be simple but as a beginner I'm just not getting there. I'll try and explain as best I can. I'm on mobile on a work break and can't post my actual data for work related reasons.

I have a table with a column of offered items, and a column of taken items, and each row is one person. For example Fred is offered an apple, and takes a banana. Betty is offered a pear and takes an orange. I have measures that count the offered and taken items, with results kind of like:

Offered:

Pear 5

Apple 4

Banana 8

Total 17

Taken:

Pear 6

Apple 3

Banana 8

Total 17

I want to calculate the % of fruit taken. So Pear 120%, Apple 75%, Banana 100%.

I had a try at doing a count of each column and displaying as a table with the Taken as rows, but I just got 100% for each row so I'm clearly going wrong.

Can anyone help?

Sorry that I can't post any actual data!

Re: Dax - division of different but related items

uzuntasgokberk — Mon, 14 Jul 2025 12:13:04 GMT

Hello MojoJojo15 ,

Based on your data structure, I believe it looks like this: a list of people with two columns—Offered and Taken—such as Fred with Apple (Offered) and Banana (Taken), Mary with Apple (Offered and Taken), and so on. You can unpivot the "Offered" and "Taken" columns to create a normalized table with three columns: Person, Item, and ItemType (either Offered or Taken). Once unpivoted, you can use the following DAX measures to calculate the counts:

Dax formula:

Offered =
CALCULATE(
COUNTROWS('Table'),
'Table'[ItemType] = "Offered"
)

Taken =
CALCULATE(
COUNTROWS('Table'),
'Table'[ItemType] = "Taken"
)

Percent Taken =
DIVIDE( [Taken], [Offered] )

Re: Dax - division of different but related items

Jihwan_Kim — Fri, 11 Apr 2025 03:03:59 GMT

Hi,

I am not sure how your semantic model looks like, but I tried to create a sample pbix file like below.

Please check the below picture and the attached pbix file.

offered item count: = COUNTROWS( data )

taken item count: = VAR _offer = VALUES ( data[offered_item] ) VAR _t = CALCULATETABLE ( data, REMOVEFILTERS ( data[offered_item] ) ) RETURN COUNTROWS ( FILTER ( _t, data[taken_item] IN _offer ) )

percentage: = DIVIDE([taken item count:], [offered item count:] )

Re: Dax - division of different but related items

techies — Fri, 11 Apr 2025 07:40:51 GMT

Hi MojoJojo15 please check this

create a disconnected table like this

FruitList =

DISTINCT(

UNION(

SELECTCOLUMNS(Items, "Fruit", Items[Offered Item]),

SELECTCOLUMNS(Items, "Fruit", Items[Taken Item])

)

create measures as this

offered counts = SUMX(

VALUES(FruitList[Fruit]),

CALCULATE(

COUNTROWS(Items),

FILTER(Items, items[Offered Item] = FruitList[Fruit])

)

Taken Counts =

SUMX(

VALUES(FruitList[Fruit]),

CALCULATE(

COUNTROWS(Items),

FILTER(Items, Items[Taken Item] = FruitList[Fruit])

)

% Taken =

DIVIDE([Taken Count], [Offered Count])

Re: Dax - division of different but related items

Anonymous — Mon, 14 Apr 2025 15:46:46 GMT

Hi MojoJojo15

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

Re: Dax - division of different but related items

MojoJojo15 — Tue, 15 Apr 2025 07:57:26 GMT

Thank you so much!!

This worked perfectly with a couple of minor tweaks for my data set 🙂

Thanks to everyone else for their suggestions too.