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.
Dear All,
I have a measure in SSAS Tabular Cube.
Measure:= (Sum(Quantity)/DistinctCount(Transactions)) * 1000
I am displaying data in Power BI Matrix as
Both Column and Line Total are wrong. Accoding to my understanding Total are coming wrong due to Expression in a measure. To solve this I tried , All Function. Calculate separately Quantity and Transactions. Also try All(Product Types), All(Store) in DAX Calculation but I am not able to get the Correct Result.
Quantity and Transaction are stored in Fact Sales and both Dimentions are Attached with Fact Sales.
I see solutions From the Power BI Forum but not able to get the close sokution.
Appreciate immeidate help.
Azhar
Solved! Go to Solution.
By using Sumx , We can have the correct Total, but issue with SUMX is Performance.
Right now I am using SUMX and Its works for me. Can some one tell me how I can improve the performance.
Measure=SumX(Sales, Quantity * Transactions) * 1000.
@Anonymous wrote:Dear All,
I have a measure in SSAS Tabular Cube.
Measure:= (Sum(Quantity)/DistinctCount(Transactions)) * 1000I am displaying data in Power BI Matrix as
Both Column and Line Total are wrong. Accoding to my understanding Total are coming wrong due to Expression in a measure. To solve this I tried , All Function. Calculate separately Quantity and Transactions. Also try All(Product Types), All(Store) in DAX Calculation but I am not able to get the Correct Result. ShowBox Mobdro TutuApp
Quantity and Transaction are stored in Fact Sales and both Dimentions are Attached with Fact Sales.
I see solutions From the Power BI Forum but not able to get the close sokution.
Appreciate immeidate help.
Azhar
I am utilizing SUMX and Its works for me. Can somebody disclose to me how I can enhance the execution.
Measure=SumX(Sales, Quantity * Transactions) * 1000.
Hi azhariqbal499,
Could you share some sample data of your fact table and dimension tables and current DAX formula you are using?
Regards,
Jimmy Tao
StoreKey | ProductTypeKey | Quantity | Transactions |
1 | 1 | 25 | 12 |
2 | 2 | 15 | 10 |
1 | 3 | 12 | 10 |
1 | 4 | 25 | 12 |
2 | 5 | 15 | 10 |
1 | 6 | 12 | 10 |
Data is Quite simple. I think we needs to change the DAX to avoid Filter contex of Store and Product Types.
The measres created over the above Fact is Measure:= (Sum(Quantity)/DistinctCount(Transactions)) * 1000
and we want to display total of measure on Rows and Columns.
Thanks
Azhar
What is your expected output?
In my Question I have shown the Matrix.
What is the expected output based on the sample fact table data you showed? I'm asking since transaction details are not available in your original question.
By using Sumx , We can have the correct Total, but issue with SUMX is Performance.
Right now I am using SUMX and Its works for me. Can some one tell me how I can improve the performance.
Measure=SumX(Sales, Quantity * Transactions) * 1000.
Just make sure you don't use a measure inside SUMX if you are iterating over a fact table. You should be referring direct columns.
Actualy We needs to have DistinctCount of Transactions and If I use
Sumx(Sales,(Sum(Sales Amount)/DistinctCount(Transactions) ) * 1000) then It gives me wrong Values.
Why don't you try the below code (assuming you have s a Product/Store dimensions). If the number of unique values of Store and Product Types are pretty less the Measure should be fast:
Base Measure = (SUM('Sales'[Quantity])/DISTINCTCOUNT('Sales'[Transactions]))*1000 Measure = SUMX ( SUMMARIZE ( 'Sales', 'Product'[Product Type], 'Store'[Store Name] ), [Base Measure] )
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 |
---|---|
113 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |