cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
bartus1
Helper I
Helper I

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

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
Microsoft
Microsoft

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. 

Thanks for your input:

 

Not sure if I get this right...

 

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

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. 

I'll try

 

DS1 -> TimeTable <- DS2

                    ^

                     |

              Time Slicer

 

 

DS1-TimeTable-DS2 works well.

Helpful resources

Announcements
September Update

Check it Out!

Click here to learn more about the September 2022 updates!

Power BI Show episode 9

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power BI Dev Camp Session 26

New Date - Check it Out!

Mark your calendars and join us on Thursday, October 6 at 11a PDT for a great session with Ted Pattison!

Top Solution Authors