Rows and Column Totals are wrong in a Power BI Matrix

[Anonymous]

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

[mobriladigo]

---

@Anonymous wrote:

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. 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.

[v-yuta-msft]

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

[Anonymous]

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

[AkhilAshok]

What is your expected output?

[Anonymous]

In my Question I have shown the Matrix.

[AkhilAshok]

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.

[Anonymous · Accepted 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.

[AkhilAshok]

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.

[Anonymous]

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.

[AkhilAshok]

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]
)