cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
bartus1 Regular Visitor
Regular Visitor

Calculate and show monthly ratio - Total Transactions to CC transaction

Hi,

 

I have records of monthly sales

 

Sale#   Date       SaleType

012      1-1-16    CC

023      1-2-16    Cash

123      1-5-16    CC

543      1-9-16    Cash

987      1-8-16    Cash

 

and so forth...

 

Need to calculate and show Total number of sales per month to CC (credit card) count of sales per month.

 

1.  Need to get total count of sale# per month.  Ex:  123-Jan,16;  232-Feb,16;

2.  Need to get total count of CC sales per month. Ex:  24-Jan,16;  32-Feb 16;

3.  Display on chart the above... 123/24 - Jan16;  232/32 - Feb 14

 

I've done 2 datasets for now.  1st with all sales, 2nd with CC sales only.  Problem - time slicer only controls date filed from 1 dataset.  

 

Anyone can suggest anything else?

1 ACCEPTED SOLUTION

Accepted Solutions
srinivt Regular Visitor
Regular Visitor

Re: Calculate and show monthly ratio - Total Transactions to CC transaction

I was thinking something like DS1 -> DateTable <- DS2. Not sure what the content of your TimeSlicer table looks like. When I say date table, it is a table with every row containing a unique date in the date range and has columns like Year, Month, Quarter, Day etc. columns that you can use to slice by. 

View solution in original post

5 REPLIES 5
srinivt Regular Visitor
Regular Visitor

Re: Calculate and show monthly ratio - Total Transactions to CC transaction

Ensure that you have a DimDate table (with contiguous dates with day granularity for the date range you care). Then have a outgoing Many-To-One Single-Directional relationship between the Dataset1 and DimDate table using the date column. Similarly build another one for the Dataset2. 

bartus1 Regular Visitor
Regular Visitor

Re: Calculate and show monthly ratio - Total Transactions to CC transaction

Thanks for your input:

 

Not sure if I get this right...

 

DS1 - DateTable1 - TimeSlicer - DateTable2 - DS2 ???

srinivt Regular Visitor
Regular Visitor

Re: Calculate and show monthly ratio - Total Transactions to CC transaction

I was thinking something like DS1 -> DateTable <- DS2. Not sure what the content of your TimeSlicer table looks like. When I say date table, it is a table with every row containing a unique date in the date range and has columns like Year, Month, Quarter, Day etc. columns that you can use to slice by. 

View solution in original post

bartus1 Regular Visitor
Regular Visitor

Re: Calculate and show monthly ratio - Total Transactions to CC transaction

I'll try

 

DS1 -> TimeTable <- DS2

                    ^

                     |

              Time Slicer

 

 

bartus1 Regular Visitor
Regular Visitor

Re: Calculate and show monthly ratio - Total Transactions to CC transaction

DS1-TimeTable-DS2 works well.

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors