Dax - division of different but related items

[MojoJojo15]

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!

[v-shamiliv]

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.

[techies · Accepted solution]

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

[MojoJojo15]

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.

[Jihwan_Kim]

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

[uzuntasgokberk]

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

Best Regards,
Gökberk Uzuntaş

LinkedIn: https://www.linkedin.com/in/g%C3%B6kberk-uzunta%C5%9F-b43906198/

Medium: https://medium.com/@uzuntasgokberk

İf this post helps, then please consider Accept it as solution and kudos to help the other members find it more quickly.