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.
Hi
I have a two tables one is FactSales and one is DimCust.
FactSales
Customer # | Season | Units Sold |
123 | S20 | 10 |
123 | S20 | 5 |
234 | S20 | 15 |
234 | S19 | 20 |
345 | S20 | 5 |
123 | S19 | 10 |
345 | S20 | 15 |
DimCust
Customer # | Customer Name | Account Mng # | Account Mng Name |
123 | Store A | 987 | Johny Smith |
234 | Store B | 999 | Peter Lee |
345 | Store C | 987 | Johny Smith |
The relationship is built on the Customer #. I would like to calculate Sale per Account Mng in the S20 Season
I was trying something like
SalePerAcctMng =
Var sum = CALCULATE(SUM(‘FactSales’[Units Sold]),’FactSales’[Season]=”S20”)
Return
CALCULATE(x,VALUES(‘DimCust’[Account Mng #]
But it doesn’t really work the way I want. When I put the measure in a table it will show something like this when I put it in the table
Acct Mng Name | Customer Name | Units Sold | SalePerAcctMng |
Johny Smith | Store A | 25 | 35 |
Johny Smith | Store C | 20 | 35 |
|
|
|
|
|
|
|
|
|
|
|
|
Can someone help? Thanx
Hi @Anonymous ,
You may try to create measure like DAX below.
SalePerAcctMng = CALCULATE(SUM(FactSales[Units Sold]),FILTER(ALLSELECTED(FactSales),FactSales[Season]="S20"&&FactSales[Customer #]=MAX(DimCust[Customer #])))
Best Regards,
Amy
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @v-xicai
Thanx for quick response but it it not what i need. It gives me the max but what i need is to have the sum of all customer per that Account Manager. ... the number in the SalePerAcctMng should be same numbers for the same Account manager .
So I need the Sum of Units sold in S20 to iterate over the Table of Account Managers
Hi @Anonymous ,
I am not sure what desired result would you want, could you please share your sample data and desired output screenshots for further analysis? You can also upload sample pbix to OneDrive and post the link here. Do mask sensitive data before uploading.
Please read this post to get your answer quickly: How to Get Your Question Answered Quickly.
Best Regards,
Amy
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 |
---|---|
108 | |
100 | |
78 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
94 | |
84 | |
67 |