topic Calculating Total of a column inside virtual table in DAX Commands and Tips

Calculating Total of a column inside virtual table

Anonymous — Mon, 08 Jan 2024 17:35:41 GMT

Greetings everyone,

I have virtual table that looks like this:

Each row is a product that belong to same product family (data is only partially visible here). "Revenue" and "Total Regional List" Price exist in the main Data Table.

Next calculateions are virtual, used as measures.

1) I have first calculated total Revenue for this family, which is 858.317,55.

2) Next i have calculated "Level Factor Weight" for each item, which is "Revenue" / "Sales Revenue per Year by Family".

3) And finally i have calculated "Weighted Target List Price", which is "Total Regional List Price" * "Level Factor Weight". So far so good.

Now i would like to calculated total or sum of "Weighted Target List Price", basically 0,06 + 0,03 + 0,00 + 0,65... (just as one see the numbers inside this table) and that is where i have problems.

When i put "Total Regional List Price" and "Weighted Target List Price" in one virtual table without product numbers, they show the same result. Any hint would be very appreciated.

Sincerely,

Pavlo

Re: Calculating Total of a column inside virtual table

v-weiyan1-msft — Wed, 10 Jan 2024 02:25:30 GMT

Hi Anonymous ,

Based on the information you provided, I did a simple test to reproduce your problem.
You can try right clicking on the field and changing the aggregation to Don't summarize and see if that helps.
For details you may check the screenshot below:

If the ProductNumber column is not added, the situation occurs in the following figure. Does this affect your desired result?

For further details, please check the attachment.
Please correct me if I misunderstood your needs.

Best Regards,
Yulia Yan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Calculating Total of a column inside virtual table

Anonymous — Wed, 10 Jan 2024 14:13:08 GMT

Hi v-weiyan1-msft ,

Thanks, your solution works for the dataset you have created.

I have it a little more complicated with additional tables that filter item number and pricing category.

I have included both into your dataset, and the results are different now.

"Sales Revenue per Year by Family" is calculated using whole dataset, instead of chosen pricing family. And of course "Sum of Weighted Price List" is different now. In the previous dataset it was around 95.

I have tried to modify Sales Revenue per Year by Family measure by changing ALL('Table') to ALL('Table (2)'), which fixes issue with Sales Revenue per Year by Family, however "Sum of Weighted Price List" is completely wrong then.

I would be very glad if you could take another look at the file. I provided the modified dataset below.

Dataset.pbix

Sincerely,

Pavlo