User Profile
DAX0110
Resolver V
Joined 8 years ago
User Widgets
Contributions
Re: Sales - stock Calculation
Hi Iamnvt, Good to see that you can debug my DAX formula - it's always harder to troubleshoot someone else's work than starting over from scratch yourself, so good work! As for FIRSTNONBLANK - you can also use SELECTEDVALUE if doing this in Power BI Desktop. It has to be used because there is only a filter context at that point. Try remove it and see what happens. The pivot table after removing date? Well it's still working. This is my screenshot:Re: Issue Presenting Data
Hi mclougb6, I'm assuming that your visual contains two filtering dimensions: month, and hierarchy (no products). The following measures will calculate two numbers for each hierarchy: number of products with no sales in that month, and number of products whose collective sales account for 5% or less in that month This is a draft solution that's tested over my own data model, so some adjustments may be required to adapt to yours. And a final heads-up: these measures could take some time to calculate, depending on the size of your data. Please create three measures : Total Sales := SUM( Sales[Net Revenue] ) Count of Products With No Sales := /* per month and per hierarchy (category) */ VAR countOfProducts = SUMX( VALUES( 'Master Data'[Material Number] ) , IF( ISBLANK([Total Sales]), 1, 0 ) ) RETURN countOfProducts Count of Products in Bottom 5% := /* per month and per hierarchy (category) */ VAR threshold = 0.05 VAR monthlyHierarchyTotalSales = [Total Sales] VAR amountThreshold = monthlyHierarchyTotalSales * threshold VAR countOfProducts = SUMX( VALUES('Master Data'[Material Number]) , VAR thisProductSales = [Total Sales] VAR lesserProductsSales = SUMX( VALUES('Master Data'[Material Number]) , VAR eachProductSales = [Total Sales] RETURN IF( eachProductSales < thisProductSales , eachProductSales , 0 ) ) VAR runningSales = lesserProductsSales + thisProductSales RETURN IF( ISBLANK(thisProductSales) , 0 , IF( runningSales > amountThreshold, 0, 1 ) ) ) RETURN countOfProductsRe: Issue Presenting Data
Hi mclougb6, from a high level modeling perspective, I'd do it this way: Given the calculation context is one month and one hierarchy : Compute the total sale for the hierarchy for the month, call this T For each product in the hierarchy, compute the per-product sum of sales for that month ... call this P Additionally, for products that have no sales in that month, assign them the value of 0 for P Then rank these P values into ascending order - ie the worst-performing products are ranked first Next, compute a running sum for each product, by adding up all the P values for the current product and all the other products that are ranked earlier ... call this R Now we can compute the bottom-N% position of each product: N = R / T All products that are in the bottom 5% of the montlhly hierarchy sale will have N values <= 0.05 Finally, you can count the number of these products per hierarchy per month. Some DAX gurus in this forum can probably write out a single measure that's shorter than all this ;)Re: Currency conversion as per exchange rate in Power BI desktop
Hi MSAtique, I'm afraid "currency conversion" doesn't happen automagically in Power BI, as much as we wish it could. You have to handle it just like any other regular kind of data. The blog post below shows how to let users change the "reporting currency": https://www.kasperonbi.com/currency-conversion-in-dax-for-power-bi-and-ssas/
