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
andy_pat
Helper I
Helper I

Dynamic DAX Division by Category and Slicer

I am trying to build a dynamic measure with a toggle as below:  Its is likely simple for pros, but can't seem to figure out the DAX way.
See PBIX attached - https://ufile.io/n4nji186 
Current measure: 

 

 

 

sum Dynamic = 
VAR toggle =
    SELECTEDVALUE ( 'Select Div'[Val],0 )

return( CALCULATE(
            DIVIDE(SUM('Table'[sale]), 0.5)
)
)

 

 

 


Required measure:

if toggle = Divide: 
when category in A, C - divide sum(sale) by 1 (in other words don't divide, just keep the numerator as is)
when category = B = divide sum(sale) by 0.5


If toggle = No_Divide : 
Do nothing and calculate the sum(sale) without any division.  



Thanks in advance!

Andy

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@andy_pat ,

As a new measure

new measure =
Switch( True() ,
max(Table[category]) in {"A","C"} , sum(sale)/1,
max(Table[category]) in {"B"} , sum(sale)/0.5,
sum(sale))

 

As a new colum
new column =
Switch( True() ,
(Table[category]) in {"A","C"} , (sale)/1,
(Table[category]) in {"B"} , (sale)/0.5,
(sale))

View solution in original post

4 REPLIES 4
amitchandak
Super User
Super User

@andy_pat ,

As a new measure

new measure =
Switch( True() ,
max(Table[category]) in {"A","C"} , sum(sale)/1,
max(Table[category]) in {"B"} , sum(sale)/0.5,
sum(sale))

 

As a new colum
new column =
Switch( True() ,
(Table[category]) in {"A","C"} , (sale)/1,
(Table[category]) in {"B"} , (sale)/0.5,
(sale))

Hello I would like to make a dynamic division of the average daily sales, I have a summary table by the days of each month. But I need you to change the division of the total amount of sales by the number of days of the month. For example 1

Total sales September = 100

Days of the month of September = 30

Result 100/30

example 2

Total sales October = 100

Days of the month of September = 31

result 100/31

Thank you.

Thanks for the help! This worked partially. The problem is that the individual category calculation changes correct, but the total of all categories (matrix) is not adjusted. How can we get the category change to be retained in the Total  Any other ideas?

Sum Dynamic =
VAR toggle = SELECTEDVALUE('Select Div'[Val])

return (
Switch( True() ,
max('Table'[category]) in {"A","C"} && toggle = "Divide", sum('Table'[sale]),
max('Table'[category]) in {"B"} && toggle = "No_Divide", sum('Table'[sale])/0.5,
sum('Table'[sale])
)
)
pranit828
Community Champion
Community Champion

Hi @andy_pat 

 

IF(('Table'[toggle] = "Divide" && 'Table'[category] in {"A","C"}) || 'Table'[toggle] = "No_Divide", SUM('Table'[sale]), 

           IF('Table'[toggle] = "Divide" && 'Table'[category] = "B", DIVIDE(SUM('Table'[sale]), 0.5),0

          )

  )





PBI_SuperUser_Rank@1x.png


Hope it resolves your issue? 
Did I answer your question? Mark my post as a solution!

Appreciate your Kudos, Press the thumbs up button!!
Linkedin Profile

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.