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. 

View solution in original post

I'll try

 

DS1 -> TimeTable <- DS2

                    ^

                     |

              Time Slicer

 

 

DS1-TimeTable-DS2 works well.

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Top Solution Authors
Top Kudoed Authors