Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

add number of zeros dynamically based on the max value(no of digits) in column ssas dax

Hi Team,

 

I need to add number of zero dynamically into the calculations based on the max value (no of digits before decimal) point.

 

Dax Reference link: https://dax.do/3B3JEwkIK0m0P6/

 

Logic: For the below snapshot, 

  1. unit_price column having max value 4249567.30 we need to calculate number of digits before decimal point. i.e. 4249567=7( no of digits)
  2. Sales Amount column having max value 5371682.10 we need to calculate number of digits before decimal point. i.e. 5371682=7( no of digits)

 

 

RGV538_0-1647947169780.png

 

after getting no of digits then we need to use the same in below calculation (Dynamic zeros in red color below calculation)

Sample Query

"UnitPrice_Column",
FIXED ( [unit_Price], 2, 1 ) + 10000000 // 4249567 (i.e, Number Of Digits=7)
//Number of zeros = number of digits
,
"Sales_Column",
FIXED ( [Sales Amount], 2, 1 ) + 10000000 // 5371682 (i.e, Number Of Digits=7)
//Number of zeros = number of digits

 

 

 

I am using this query now

DEFINE
VAR Req_columns =
ADDCOLUMNS (
SUMMARIZECOLUMNS (
'Product'[Brand],
Customer[Occupation],
"unit_Price", CALCULATE ( SUM ( Sales[Unit Price] ) ),
"Salesamount", [Sales Amount]
),
"UnitPrice_Column",
FIXED ( [unit_Price], 2, 1 ) + 10000000 // 4249567 (i.e, Number Of Digits=7)
//We need to consider max value from [unit_Price] and addition with number of zeros 
,
"Sales_Column",
FIXED ( [Sales Amount], 2, 1 ) + 10000000 // 5371682 (i.e, Number Of Digits=7)
//We need to consider max value(5371682) from [Sales Amount] and addition with number of zeros
)

EVALUATE
Req_columns
ORDER BY [Sales Amount] DESC

 

Thanks In Advance

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@Anonymous , You can meausre like

M1 = maxx(summarize(allselected(sales), 'Product'[Brand],
Customer[Occupation] , "_1",  CALCULATE ( SUM ( Sales[Unit Price] ) ) ) , [_1])

 

M2 = Power(10, len([M1]&"")

 

that will give you desired number

 

you can add code in summarize column too

 

View solution in original post

1 REPLY 1
amitchandak
Super User
Super User

@Anonymous , You can meausre like

M1 = maxx(summarize(allselected(sales), 'Product'[Brand],
Customer[Occupation] , "_1",  CALCULATE ( SUM ( Sales[Unit Price] ) ) ) , [_1])

 

M2 = Power(10, len([M1]&"")

 

that will give you desired number

 

you can add code in summarize column too

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors