topic Re: Calculating Stores by Transaction Range in DAX Commands and Tips

Calculating Stores by Transaction Range

joaopalhares — Thu, 05 Aug 2021 00:15:20 GMT

Hello,

I would like to ask for help on the following problem:

I have an excel database which have the total number of transactions per store and date.

Then, i summarize the total number of transactions per store on power bi through the measure sum(transactions).

Generating this visual:

Then, I want to count how many stores have more then 100 transactions and how many have equal or less to 100 transactions. Getting a visual like this (i did in excel just as an example):

You can find all the files (.pbix and excel) on the link below for better visualization and analysis:

Files

Re: Calculating Stores by Transaction Range

wdx223_Daniel — Thu, 05 Aug 2021 03:15:43 GMT

create a dimension table

and create a measure

Re: Calculating Stores by Transaction Range

Jihwan_Kim — Thu, 05 Aug 2021 03:25:01 GMT

More than 100 : =

VAR _newtable =

ADDCOLUMNS ( VALUES ( Sheet1[Store] ), "@transactions", [Total Transactions] )

RETURN

COUNTROWS ( FILTER ( _newtable, [@transactions] > 100 ) )

Equal or less than 100 : =

VAR _newtable =

ADDCOLUMNS ( VALUES ( Sheet1[Store] ), "@transactions", [Total Transactions] )

RETURN

COUNTROWS ( FILTER ( _newtable, [@transactions] <= 100 ) )

Link to the sample pbix file

Re: Calculating Stores by Transaction Range

joaopalhares — Thu, 05 Aug 2021 17:12:50 GMT

Hello JihwanKim . Thank you for the support given.

It worked really nice. I've tried both ways, the one provided by you and wdx223_Daniel.

There is one other thing i would like your help to evaluate if it is possible:

Is it possible to, when i select (press) the number of stores count to filter the other table?

For instance, when i click the number 1 (more than 100) to filter the other table showing only store B.

Your help would be awesome. Thanks in advance.

Updated files on the link below.

Updated Files

Re: Calculating Stores by Transaction Range

joaopalhares — Thu, 05 Aug 2021 17:13:40 GMT

Hello wdx223_Daniel wdx223_daniel . Thank you for the support given.

It worked really nice. I've tried both ways, the one provided by you and Jihwan_Kim .

There is one other thing i would like your help to evaluate if it is possible:

Is it possible to, when i select (press) the number of stores count to filter the other table?

For instance, when i click the number 1 (more than 100) to filter the other table showing only store B.

Your help would be awesome. Thanks in advance.

Updated files on the link below.

Updated Files

Re: Calculating Stores by Transaction Range

wdx223_Daniel — Fri, 06 Aug 2021 00:22:50 GMT

add a new column in table of sheet1

and, create a relationship between the new column to dimTable

then change the measure code of StoreCount to

Re: Calculating Stores by Transaction Range

joaopalhares — Fri, 06 Aug 2021 00:40:45 GMT

Thank you!