topic Total on dynamic measure in DAX Commands and Tips

Total on dynamic measure

Anonymous — Tue, 08 Nov 2022 06:30:08 GMT

Hi

I'm working on a dataset for sales dept. In our business we have both internal and external sales both present in the datamodel, summed up in a single measure.

I have a Dimension named transaction_type that contains a value for internal/external

The business would like that the sales measure show only external sales, unless filtered on internal, or transaction type being used within a visualization.

Using SelectedValue I can build the measure to show only external unless filtered on internal or shown in a visualization.

Sadly this shows the wrong total in the cases where both internal and external is present.

I understand why the total is showing as only External (because the total is not a selectedvalue and therefore reverts to "Eksternal")

Is there a way to force the total to sum up what is actually shown on screen?

Re: Total on dynamic measure

ValtteriN — Mon, 14 Nov 2022 09:49:58 GMT

Hi,

Table totals use dax calculation to get their values and thus aren't considering what is happening within the visual. Because of this I don't think you can get your calculation to sum external values when a visual is not filtered and the same time calculate total values for both internal and external.

For more information on totals check this article by SQLBI: Obtaining accurate totals in DAX - SQLBI

Re: Total on dynamic measure

Anonymous — Wed, 09 Nov 2022 03:27:14 GMT

Hi Anonymous ,

You can create another new measure as below and put this new measure to replace the original measure onto the table visual...

New Measure = SUMX ( VALUES ( 'dim_transaction_type'[TransactionTypeCode] ), [Invoiced Amount] )

In addition, you can refer the following links to try to solve your problem...

Why Your Total Is Incorrect In Power BI - The Key DAX Concept To Understand

Dax for Power BI: Fixing Incorrect Measure Totals

If the above one can't help you get the desired result, please provide some sample data in your table 'fact_sales_invoice_line' and 'dim_transaction_type' (exclude sensitive data) with Text format and your expected result with backend logic and special examples. By the way, is there any relationship created between the table 'fact_sales_invoice_line' and 'dim_transaction_type'? It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards

Re: Total on dynamic measure

Anonymous — Mon, 14 Nov 2022 08:47:18 GMT

Hi Anonymous ,

Whether your problem has been resolved? If yes, could you please mark the helpful post as Answered? It will help the others in the community find the solution easily if they face the same problem as yours. Thank you.

Best Regards