Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
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
Solved! Go to Solution.
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
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
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:
Here is the demo , please try it:
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.
@devparas1 Please use below measure
Measure =
VAR _ack = SUM('Table'[Ack Time])
VAR _qty = SUM('Table'[Qty])
RETURN DIVIDE(_ack,_qty,0)
User | Count |
---|---|
125 | |
106 | |
99 | |
63 | |
60 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |