Multiplying Rates with Measure

[likhithar]

Hello All,

I have Sales Value till Last Year(Till March -2021).Fin Year Starts from April-2021

In B1, I have Sales Previous Year(Cumulative),from B2 (April-2021,202104) it's for the Month, In C Column I'm doing Cumaltive sum

i.e 

C2=B1+B2

C3=C2+B3

.

.

.

The DAX I used for the above Logic is 

Cur Yr YTD =

CALCULATE(SUM(SALES[SALES]),FILTER(ALL(DATE TABLE),DATE TABLE[FIN YEAR]=SELECTEDVALUE(DATE TABLE[FIN YEAR])&&DATE TABLE[MONTH NO]<=MAX(DATE TABLE[MONTH NO])))

PREV YR VALUE ADDED TO CUR YR CUMULATIVE(To Derive 'C' Column in the above snap)

Sales SUM = 
var a = SUMMARIZE(FILTER(ALL(DATE TABLE),DATE TABLE[YEAR] = MAX(DATE TABLE[YEAR])&&DATE TABLE[MONTH NO]=MAX(DATE TABLE[MONTH NO])),DATE TABLE[YEAR],DATE TABLE[MONTH NO],"RB",[Cur Yr YTD])
var b = CALCULATE(SUM(SALES[SALES]),FILTER(ALL(DATE TABLE),DATE TABLE[YEAR]=MAX(DATE TABLE[YEAR])-1&&DATE TABLE[MONTH NO]<=12))

return 

SUMX(a,[Cur Yr YTD])+b

D Column is again Cumulative Sum

D2= C1+C2

D3=D2+C3

D4=D3+C4

.

.

The DAX I used for the Above Logic is

Sales SUM Cumulative = 
var a = SUMMARIZE(FILTER(ALL(DATE TABLE),DATE TABLE[YEAR] = MAX(DATE TABLE[YEAR])&&DATE TABLE[MONTH NO]<=MAX(DATE TABLE[MONTH NO])),DATE TABLE[YYYYMM],DATE TABLE[YEAR],DATE TABLE[MONTH NO],DATE TABLE[MONTH NAME],"RB",[Sales SUM])
var b = CALCULATE(SUM(SALES[SALES]),FILTER(ALL(DATE TABLE),DATE TABLE[YEAR]=MAX(DATE TABLE[YEAR])-1&&DATE TABLE[MONTH NO]<=12))

return 

SUMX(a,[Sales SUM])+b

As per the above DAX, I'm getting the correct total as required. My Month selection is 202109(September-2021)

I have Rate at Vehice,Vehicle Type(Ex: CARS-NEW) and certain Rate for Each Month

The DAX I Used for that is

Rate = SUMX(RATE,RATE[RATE],[Sales SUM Cumulative] )*6/12

With the Rate DAX I'm getting wrong values

Rate_1 = SUMX(RATE,RATE[RATE],[Sales SUM ] )*6/12

With the Rate_1 DAX, for Sales SUM measure if I apply rates, I'm getting correct Total and correct Values.

Rate_New= SUMX(VALUES(RATE[VEHICLE-VEHICLE TYPE]),SUM(RATE[RATE]),[Sales SUM Cumulative ] )*6/12

With Rate_New I'm getting correct values at row basis ,but total is wrong.

https://community.powerbi.com/t5/Desktop/Wrong-Totals-with-SUMX-Measure/m-p/2365139#M851179

For Detailed Data Modeling,I'm attaching the Link of my Previous Question in Power BI Community.

In Fact Table, Data is available at Vehicle, Vehicle Type and Region Level for each Month. The Rate will be available at Vehicle - Vehicle Type Level for each month.

Selected Month Value to be applied to Sales SUM Cumulative Measure.

Help me out on this...

TIA

[v-chenwuz-msft]

Hi @likhithar ,

You want control the Total row to get the currect result? Maybe you can try HASONEVALUE() and if hasonevalue(),Rate_New, else expression for total rate. Please note that the filter context of the total row when you selected month.

I don't fully understand what kind of results you need. Or you could provide sample data and results, even if they are hand drawn, if you need more help.

Best Regards

Community Support Team _ chenwu zhu

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

[lbendlin]

Please provide sanitized sample data that fully covers your issue. If you paste the data into a table in your post or use one of the file services it will be easier to work with. Avoid posting screenshots of your source data if possible.

Please show the expected outcome based on the sample data you provided. Screenshots of the expected outcome are ok.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523