topic Re: need to repeat same measure value to use as denominator, % of sales calculation based on measure val in DAX Commands and Tips

need to repeat same measure value to use as denominator, % of sales calculation based on measure val

crisaldana — Mon, 26 Sep 2022 20:15:49 GMT

Total Revenue line = CALCULATE([Actuals CM],FILTER(Layout,Layout[Level 2]="TOTAL REVENUE")) -881495.70

Actuals CM = COALESCE([actuals month],[item SUBTOTAL])

new measure = DIVIDE([Actuals CM],[Total Revenue line])

Hello All,

reaching to all my power BI senseis regarding an issue that i am having with a measure.

I am looking to divide all the actuals for current month (Actuals CM) by the total revenue line for the month (total revenue line) in order to get a % of revenue measure for all items.
I tried the to create a measure (new measure) and it works but only for 3 rows. The revenue, TOTAL Revenue, and Total lines. I am guessing because the value of -881,945.70 is not repeating down to all rows.

how can I have all rows repeat the total revenue line figure or achieve the calculation for all rows to divide by this value?

Thank you for your assistance

Re: need to repeat same measure value to use as denominator, % of sales calculation based on measure val

amitchandak — Tue, 27 Sep 2022 02:06:20 GMT

crisaldana , BAsed on what I got try like

CALCULATE([Actuals CM],Layout[Level 2]="TOTAL REVENUE")

CALCULATE([Actuals CM],FILTER(all(Layout[Level 2]),Layout[Level 2]="TOTAL REVENUE"))

CALCULATE([Actuals CM],FILTER(allselected(Layout[Level 2]),Layout[Level 2]="TOTAL REVENUE"))

CALCULATE([Actuals CM],FILTER(allselected(Layout),Layout[Level 2]="TOTAL REVENUE"))

Re: need to repeat same measure value to use as denominator, % of sales calculation based on measure val

Anonymous — Tue, 27 Sep 2022 02:40:36 GMT

Hi crisaldana

Your original formula may only show data in [level2]= "TOTAL REVENUE" or the group "TOTAL REVENUE" contained in [level1], instead of returning -881.50k per row.

like this:

You can try the following formula:

Total Revenue line = CALCULATE([Actuals CM],FILTER(ALL(Layout),Layout[Level 2]="TOTAL REVENUE"))

Total Revenue line = CALCULATE([Actuals CM],FILTER(ALLSELECTED(Layout),Layout[Level2]="TOTAL REVENUE"))

Refer to:

ALL function (DAX) - DAX | Microsoft Learn

ALLSELECTED function (DAX) - DAX | Microsoft Learn

Best Regards,

Liu Yang

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

Re: need to repeat same measure value to use as denominator, % of sales calculation based on measure val

crisaldana — Mon, 03 Oct 2022 15:33:24 GMT

Thank you all for your assistance, your replies alone helped me understand more about filters and calculations. Thanks.