Forum Discussion
Calculating Stores by Transaction Range
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:
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
6 Replies
create a dimension table
and create a measure
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.
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
- More than 100 : =VAR _newtable =ADDCOLUMNS ( VALUES ( Sheet1[Store] ), "@transactions", [Total Transactions] )RETURNCOUNTROWS ( FILTER ( _newtable, [@transactions] > 100 ) )Equal or less than 100 : =VAR _newtable =ADDCOLUMNS ( VALUES ( Sheet1[Store] ), "@transactions", [Total Transactions] )RETURNCOUNTROWS ( FILTER ( _newtable, [@transactions] <= 100 ) )
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.
