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
devparas1
Regular Visitor

Measure DAX - Calculate percentage based on 2 column and Dates Slicer

Hello Community,

I am stuck at this point and not able to find anymore documentation or solution. 

 

Attached screenshot has my raw data and my Power BI report screenshot.
Ask.jpg

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

There is 2 columns "Ack Time #" and "Qty" I have to find the percentage based on those two columns but group by the PriorityID and then the value should be displayed to the graph and the Table in Power BI Desktop.

For this case I am using a DAX Measure and calculating the percentage (Ack Time # / Qty) it calulates but based on the total rows.

Now in this case I have date slicer so when the user selects certain Start and End dates it changes the total based on the dates to the Table in Power BI Desktop. But the DAX Measure for the Percentage does not catch those changes and do not update the percentage in the Table nor on the graph. It stays the same instead it should calculate the new total based on those slicer dates value. 

 

Need solution for this, stuck for literally 2 days. 

 

Thanks,

Paras 

1 ACCEPTED SOLUTION
devparas1
Regular Visitor

Thank you all for your time and support.

 

I have found the solution for this. I am sorry if I might not have been clear in my question and may have confused you.

@Anonymous and @v-yingjl 

 

The soultion to this was, as follows:

1).

Description: To include the priorityID as filter and to make two intermediate measure 

 

Code:

Intermediate 1 = SUMX(KEEPFILTERS(VALUES(‘Sheet1’[PriorityID])), CALCULATE(SUM(‘Sheet1’[Ack Time #])))

Intermediate 2 = SUMX(KEEPFILTERS(VALUES(‘Sheet1’[PriorityID])), CALCULATE(SUM(‘Sheet1’[Qty])))

FinalMeasure = DIVIDE(‘Sheet1’[Intermediate 1], ‘Sheet1’[Intermediate 2])

 

                                                                                     OR

2). 

Description: To include the priorityID as filter and to combine them into one 

 

Code:

FinalMeasure = DIVIDE(

                              SUMX(KEEPFILTERS(VALUES(‘Sheet1’[PriorityID])), CALCULATE(SUM(‘Sheet1’[Ack Time #])))

                               ,

                               SUMX(KEEPFILTERS(VALUES(‘Sheet1’[PriorityID])), CALCULATE(SUM(‘Sheet1’[Qty]))) 

                           )

 

I hope this helps someone in future where there is a date slicer or some other kind of slicer. And you need to calculate based on that filtered values on the  graph.

 

Thanks,

Paras

 

View solution in original post

3 REPLIES 3
devparas1
Regular Visitor

Thank you all for your time and support.

 

I have found the solution for this. I am sorry if I might not have been clear in my question and may have confused you.

@Anonymous and @v-yingjl 

 

The soultion to this was, as follows:

1).

Description: To include the priorityID as filter and to make two intermediate measure 

 

Code:

Intermediate 1 = SUMX(KEEPFILTERS(VALUES(‘Sheet1’[PriorityID])), CALCULATE(SUM(‘Sheet1’[Ack Time #])))

Intermediate 2 = SUMX(KEEPFILTERS(VALUES(‘Sheet1’[PriorityID])), CALCULATE(SUM(‘Sheet1’[Qty])))

FinalMeasure = DIVIDE(‘Sheet1’[Intermediate 1], ‘Sheet1’[Intermediate 2])

 

                                                                                     OR

2). 

Description: To include the priorityID as filter and to combine them into one 

 

Code:

FinalMeasure = DIVIDE(

                              SUMX(KEEPFILTERS(VALUES(‘Sheet1’[PriorityID])), CALCULATE(SUM(‘Sheet1’[Ack Time #])))

                               ,

                               SUMX(KEEPFILTERS(VALUES(‘Sheet1’[PriorityID])), CALCULATE(SUM(‘Sheet1’[Qty]))) 

                           )

 

I hope this helps someone in future where there is a date slicer or some other kind of slicer. And you need to calculate based on that filtered values on the  graph.

 

Thanks,

Paras

 

v-yingjl
Community Support
Community Support

Hi @devparas1 ,

For this case, you can just modify your measure like this:

 

Percentage (Ack Time/Qty) =

DIVIDE (

    CALCULATE ( SUM ( 'table'[Qty] ) ),

   CALCULATE ( SUM ( 'table'[Ack Time #] ) ),

    0

) * 100

 

You will get the following result:

measure1.png

 

measure2.png

 

Here is the demo , please try it:

PBIX 

 

Best Regards,

Yingjie Li

 

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

Anonymous
Not applicable

@devparas1 Please use below measure

Measure = 
VAR _ack = SUM('Table'[Ack Time])
VAR _qty = SUM('Table'[Qty])
RETURN DIVIDE(_ack,_qty,0)

 

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.