topic Re: Subtotal of single items instead of calculated formula in a table in DAX Commands and Tips

Subtotal of single items instead of calculated formula in a table

jordanesqu — Tue, 15 Sep 2020 16:01:37 GMT

Hi All,

I calculated VolPriceMix which work on a single item but then subtotal display also a calculated result instead of sum of particular items.

I calculated Volume as a Delta Quantity * Avg Price Previous year

Vol_ = [∆ HL Ist]*[AVG Price VJ]

Then I tried to sum those single items using HASONEVALUE but I noticed that in case of no Quantity in current year(THL Ist) formula does not return a value in some cases.

Vol = IF(HASONEVALUE(PBI_JC[Product.]); [Vol_]; SUMX(VALUES(PBI_JC[Product.]); [Vol_]))

Could you advise how to solve this problem or another way how to get to the subtotal?

Thanks

Re: Subtotal of single items instead of calculated formula in a table

Greg_Deckler — Tue, 15 Sep 2020 17:26:06 GMT

jordanesqu Maybe try ISINSCOPE instead?

Re: Subtotal of single items instead of calculated formula in a table

amitchandak — Wed, 16 Sep 2020 03:31:55 GMT

jordanesqu , what is issue this alone is giving

SUMX(VALUES(PBI_JC[Product.]); [Vol_])

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

Re: Subtotal of single items instead of calculated formula in a table

jordanesqu — Thu, 17 Sep 2020 13:01:07 GMT

amitchandak

I would like the Total to be calculated as a sum of single items not as a formula. For example Price is calculated as:

Price = IF(AND([Qty CY]<>0;[Qty LY]=0);0;[Qty CY]*[Delta Rev/Qty]),

which calculate for the total as 3.034,62(Qty CY)*9,94(Delta Rev/Qty) = 30.166,42

I would like however the total for Price to be the sum of Price effects of single Products:

0,00+(-348,25)+10.801,66+7.125,11+39,66+15.914,08+4.823,49+(-1.560,46)+27.357,17+26.444,96 = 90.597,42

Below the screen shots (I do not see the option to attach PBIx if you know how to do this please let me lnow and I share)