Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello everyone,
I'm new at powerBI and I have some doubts. I need to split monthly sales goals per sellers. This data will be displayed in an matrix.
I work with four simple tables: Sales fact, calendar,sales goals of the branches and branch description. e.g.:
Sales fact:
Date | Branch | Seller | Value |
01/27/2016 | 1 | Marcos | 909,90 |
01/27/2016 | 1 | Pedro | 1.000,00 |
01/27/2016 | 1 | Zacarias | 8.000,00 |
01/27/2016 | 3 | Diego | 910,00 |
01/27/2016 | 3 | Luke | 850,00 |
01/27/2016 | 3 | John | 498,00 |
01/27/2016 | 4 | Matias | 1.469,00 |
01/27/2016 | 4 | Pietra | 1.000,00 |
01/27/2016 | 4 | Samira | 6.000,00 |
02/15/2016 | 1 | Fabio | 2.199,00 |
02/15/2016 | 1 | Pedro | 2.000,00 |
02/15/2016 | 1 | Zacarias | 541,00 |
02/15/2016 | 3 | Mat | 2.500,00 |
02/15/2016 | 3 | Luke | 600,00 |
02/15/2016 | 3 | John | 210,00 |
02/15/2016 | 4 | Marcos | 1.767,00 |
02/15/2016 | 4 | Pietra | 100,00 |
02/15/2016 | 4 | Samira | 1.000,00 |
03/10/2016 | 1 | Tadeo | 1.599,00 |
03/10/2016 | 1 | Pedro | 540,00 |
03/10/2016 | 1 | Emanuel | 659,00 |
03/10/2016 | 3 | Diego | 1.009,90 |
03/10/2016 | 3 | Luke | 500,00 |
03/10/2016 | 3 | Elizabeth | 800,00 |
03/10/2016 | 4 | Matias | 1.310,00 |
03/10/2016 | 4 | Pietra | 548,00 |
03/10/2016 | 4 | Rafaela | 222,00 |
Montly Branchs Sales Goal(I only analyze by month and year):
Date | Branch | Sales Goal |
01/01/2016 | 1 | 10.000,00 |
01/01/2016 | 3 | 12.000,00 |
01/01/2016 | 4 | 10.000,00 |
02/01/2016 | 1 | 11.000,00 |
02/01/2016 | 3 | 12.000,00 |
02/01/2016 | 4 | 14.000,00 |
03/01/2016 | 1 | 8.000,00 |
03/01/2016 | 3 | 5.000,00 |
03/01/2016 | 4 | 9.000,00 |
Calendar(Has all the dates day by day from 2014 until 2017):
Date | Year | Month | Month Name |
01/01/2012 | 2012 | 1 | January |
01/01/2013 | 2013 | 1 | January |
01/01/2014 | 2014 | 1 | January |
01/01/2015 | 2015 | 1 | January |
01/01/2016 | 2016 | 1 | January |
01/01/2017 | 2017 | 1 | January |
Branch Description:
Branch | Brand | Region | State | City |
1 | Atacado | R1 | RJ | Rio de Janeiro |
3 | Varejo | R1 | RJ | Niteroi |
4 | Atacado | R3 | SP | Bauru |
Relationships:
Branch Description Sales Fact
Branch 1:* Branch
Branch Description Montly Branchs Sales Goal
Branch 1:* Branch
Calendar Montly Branchs Sales Goal
Date 1:* Date
Calendar Sales fact
Date 1:* Date
Measures:
Total sales = SUM('Sales fact'[Value]) Total Sellers = DISTINCTCOUNT('Sales fact'[Seller]) Total goals = SUM('Branchs sales goals'[Goal]) Goal per seller = [total goals]/[sellers] % Goal reached = [Total sales]/[goal per seller]
The matrix doesn't work or reports wrong values. Even when I apply the year and month filter, old sellers(from other years and who no longer work in the company) appear in the matrix.
Expected Result
The goal is to have an matrix that informs the following information(There are 3 filters in the report. Branch filter, year and month):
Seller | Total Sellers | Sales Goal | Total Sales | %Goal Reached |
Seller X | 1 | 4000 | 3000 | 75,0% |
Seller Y | 1 | 4000 | 3800 | 95,0% |
Seller Z | 1 | 4000 | 4000 | 100,0% |
Total | 3 | 12000 | 10800 | 90,0% |
I hope I have been clear in my doubts. Thank you for your attention.
Solved! Go to Solution.
First, great post, all the information needed to recreate the problem.
I have created this model and feel that it is working. A couple of things. First, make sure that your relationship between your Sales Fact table and your Branches table is cross-filtering in both directions. Second, make sure that you are using "Branch" column from your Branches table, etc. I can post a screen shot of my model or share it with you if you would like.
First, great post, all the information needed to recreate the problem.
I have created this model and feel that it is working. A couple of things. First, make sure that your relationship between your Sales Fact table and your Branches table is cross-filtering in both directions. Second, make sure that you are using "Branch" column from your Branches table, etc. I can post a screen shot of my model or share it with you if you would like.
First, I'm pretty sure I followed your guide on how to make a good post. Thanks for the guide.
I was able to solve most of the problems with your suggestion to cross-filtering in both directions. After that I researched new formulas and managed to format the matrix the way I wanted.
Thanks for the help and attention. Problem solve.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
94 | |
81 | |
66 | |
58 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |