Microsoft is giving away 50,000 FREE Microsoft Certification exam vouchers. Get Fabric certified for FREE! Learn more
Hi all,
Looking to compare the sales of two products. I want to be able to compare the total and average unit sales for stores that sell Product A only, stores that sell Product B only, and stores that sell both Product A and B. I am stuck determining the best custom measure that can filter for both the store and the product. Any insight?
I also wanted to see the distinct count of stores that sell each product and successfully wrote a customer measure for stores that sell Product A only and Product B only, but the function did not work when I tried to filter for both in one measure:
"Count of A & B Stores = CALCULATE(DISTINCTCOUNT('Portal Data'[Store]),FILTER('Portal Data',[Description]="SAUERKRAUT A" && [Description]="SAUERKRAUT B"))
How did I go wrong here? The card read the measure as Blank.
Thanks for helping us become Power BI Wizards!
Solved! Go to Solution.
@thebrinery,
I make a test using the following sample data, and create the measures below.
Count of A&B stores = CALCULATE(DISTINCTCOUNT('Portal Data'[Store]),FILTER('Portal Data','Portal Data'[Description]="SAUERKRAUT A" || 'Portal Data'[Description]="SAUERKRAUT B"))
total sales = CALCULATE(SUM('Portal Data'[Sales]),FILTER('Portal Data','Portal Data'[Description]="SAUERKRAUT A" || 'Portal Data'[Description]="SAUERKRAUT B"))
average sales = [total sales]/[Count of A&B stores]
If the above DAX don’t help, please share sample data of your table and post expected result here. Do mask sensitive data before you uploading sample data.
Regards,
Lydia
@thebrinery,
I make a test using the following sample data, and create the measures below.
Count of A&B stores = CALCULATE(DISTINCTCOUNT('Portal Data'[Store]),FILTER('Portal Data','Portal Data'[Description]="SAUERKRAUT A" || 'Portal Data'[Description]="SAUERKRAUT B"))
total sales = CALCULATE(SUM('Portal Data'[Sales]),FILTER('Portal Data','Portal Data'[Description]="SAUERKRAUT A" || 'Portal Data'[Description]="SAUERKRAUT B"))
average sales = [total sales]/[Count of A&B stores]
If the above DAX don’t help, please share sample data of your table and post expected result here. Do mask sensitive data before you uploading sample data.
Regards,
Lydia
User | Count |
---|---|
97 | |
67 | |
57 | |
47 | |
46 |