Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
Hi All,
I am very new to Power BI and am trying to create the following.
I have slicers for Sales Rep, Brands, City and Account name and a slider for dates and I would like to see the revenue in the tables based on the slicers. My data goes back to 3 years. I have created a date table for my fiscal calendar, which runs from March- Feb.
My data looks as follows.
Parent Stores | Store Name | Sales Rep | Brandname | category | city | Revenue | dates | month name | monthnum |
Parent A | Store A | Joe | Pen | Category A | Toronto | 6.95 | 09-Jul-21 | July | 7 |
Parent A | Store A | Joe | Pen | Category A | Toronto | 6.95 | 18-Jun-20 | June | 6 |
Parent A | Store B | Adam | Pen | Category A | Boston | 6.95 | 06-Aug-21 | August | 8 |
Parent B | Store C | Randy | Pencils | Category A | Los Angeles | 7.85 | 01-Jul-21 | July | 7 |
Parent B | Store C | Randy | Eraser | Category A | Los Angeles | 8.76 | 26-Jul-21 | July | 7 |
Parent B | Store C | Randy | Eraser | Category A | Los Angeles | 5.67 | 25-Jun-21 | June | 6 |
Parent B | Store D | Adam | Marker | Category C | New York | 5.67 | 19-Jul-21 | July | 7 |
Parent C | Store E | Joe | Books | Category B | Hamilton | 67.95 | 23-Jul-21 | July | 7 |
Parent C | Store F | Adam | Books | Category B | Miami | 76.21 | 13-Jul-21 | July | 7 |
Parent C | Store F | Adam | Books | Category B | Miami | 5.67 | 27-Jul-21 | July | 7 |
Parent C | Store F | Adam | Pencils | Category A | Miami | 4.32 | 12-Jun-21 | June | 6 |
Parent C | Store F | Adam | Marker | Category C | Miami | 5.67 | 18-Jun-21 | June | 6 |
I have tried a few different ways, but my data doesn't appear correctly in the table format. Ideally, when we click on the store in the first table, we would get a list of all the brands sold at that store with their respective revenues.
Any help would be greatly appreciated.
Thank-you.
Hi @user_unknown39 .
You can try the below DAX to get the desired output.
1)Current Period=CALCULATE(SUM(Table1[Column]),YEAR(Table1[date]=YEAR(TODAY( ) )).
2)LAst Year Same Period= CALCULATE(SUM(Table1[Column]),SAMEPERIODLASTYEAR(Table1[date]).
3)YTD =CALCULATE(SUM(Table1[Column]),DATESYTD(Table1[date]).
4)Last Year YTD=TOTALYTD ( SUM ( Table1[Column] ), DATEADD ( table[date], -12, MONTH ) ).
Please let me know if it helps.
If this post helps answer your question, then please do mark it as 'Accept as Solution' and give it a big thumbs up.
Reagards,
Sanket Bhagwat
Hi @SanketBhagwat ,
I don't this will work as your code applies only to only the year. I am looking at the date/day level.
So for example: If the date selected in the slider is from 04/01/2021- 06/31/2021, I would like to see the numbers for the selected period vs. the same period last year. For YTD, I will see the numbers from 03/01/2021 to 06/31/2021 as my fiscal year starts in March.
Hi @user_unknown39 ,
Try the following formula:
Current period = SUM('Table'[Revenue])
Last Year Same Period =
CALCULATE(
SUM('Table'[Revenue]),
FILTER(
ALL('Calendar'),
'Calendar'[Date] >= EDATE( MIN('Calendar'[Date]), -12 )
&& 'Calendar'[Date] <= EDATE( MAX('Calendar'[Date]), -12 )
)
)
YTD =
CALCULATE(
SUM('Table'[Revenue]),
FILTER(
ALL('Calendar'),
'Calendar'[Date] >= EDATE( DATE( YEAR(TODAY()), 1, 1 ), 2 )
&& 'Calendar'[Date] <= MAX('Calendar'[Date])
)
)
Last Year YTD =
CALCULATE(
SUM('Table'[Revenue]),
FILTER(
ALL('Calendar'),
'Calendar'[Date] >= EDATE( DATE( YEAR(TODAY()) - 1, 1, 1 ), 2 )
&& 'Calendar'[Date] <= EDATE( MAX('Calendar'[Date]), -12)
)
)
If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thanks @v-kkf-msft. How would this work with percent revenue share by the store?
I am looking to calculate the total revenue share by store
eg:
Store Name | Current Period | Last Year Same Period | Current Share | LY Share |
Store A | 100 | 75 | 67% | 60% |
Store B | 50 | 50 | 33.30% | 40% |
Thank you once again and I greatly appreciate all your help.
Hi @user_unknown39 ,
Has your problem been solved? If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
Hi @v-kkf-msft , Based on your comments, I created the following to calculate distinct counts as well. The rows look fine but the grand total at the bottom doesn't show the sum correctly. Can this be fixed?
Hi @user_unknown39 ,
Please try to use HASONEVALUE(<columnName>) or ISFILTERED(<columnName>) to change the value of the total part. Like this:
Measure =
IF(
HASONEVALUE('Table'[Parent Stores]),
[number_brands_YTD],
SUMX( ALLSELECTED('Table'[Parent Stores]), [number_brands_YTD] )
)
If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @v-kkf-msft ,
The row totals where number_brands_YTD is null are showing up wrong. I used your code to create the second column (number of listings). The rows in column 2 that is showing the grand total should actually be null.
Please see the details below.
I hope you can help me resolve this issue at the earliest.
Thanks,
Hi @user_unknown39 ,
This is due to context. I was able to reproduce your problem if I used the Parent Stores field in different tables.
You need to use the Parent Stores field from the same table.
If the problem still hasn't been solved, try adding the condition: IF( [number_brands_YTD] <> BLANK(), ....
Like this:
Measure =
IF(
[number_brands_YTD] <> BLANK(),
IF(
HASONEVALUE('Table'[Parent Stores]),
[number_brands_YTD],
SUMX( ALLSELECTED('Stores'[Parent Stores]), [number_brands_YTD] )
)
)
Best Regards,
Winniz
Hi @user_unknown39 ,
I think you need to create the following measure:
Current Share = [Current period] / SUMX( ALLSELECTED('Table'), [Current period] )
LY Share = [Last Year Same Period] / CALCULATE( [Last Year Same Period], ALLSELECTED('Table') )
If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
110 | |
109 | |
108 | |
93 | |
61 |
User | Count |
---|---|
171 | |
139 | |
133 | |
102 | |
86 |