I urgently need your help to figure out some DAX formulas.
Here is the link https://1drv.ms/u/s!AnbNXrfr-X8jdCh4I-viYzEgvpw?e=kGjuRO for a sample file pbix.
Here below please a picture in which I indicated my questions:
Many thanks in advance for your help.
Hey @EZV12 ,
where exactly are you struggling?
What did you try and how should the result for the 3 cases look like?
I don't totally understand what exactly the result should be.
I updated the pbix file, here is the link: https://1drv.ms/u/s!AnbNXrfr-X8jdeHINqfL4vNG-kY?e=WRfzpA
Below please find the picture with more explanations for my requests for DAX formulas:
Please help! Thanks.
Hello @EZV12 ,
yes, those descriptions were more clear.
I understood that you need a result like that:
Here the measures I used.
Measure for 1:
Sum Shipped Parts = CALCULATE ( SUM('KPI data'[Value]), 'KPI data'[Data] = "ShippedParts")
Measures for 2:
ServiceRate = VAR vShipments = CALCULATE ( SUM('KPI data'[Value]), 'KPI data'[Data] = "Shipments") VAR vShipmentDelay = CALCULATE ( SUM('KPI data'[Value]), 'KPI data'[Data] = "ShipmentDelay") RETURN DIVIDE( (vShipments - vShipmentDelay ) , vShipments)
ServiceRate Target = DIVIDE(CALCULATE ( SUM('KPI Target'[Value]), 'KPI Target'[KPI] = "ServiceRate") , 100)
Measure for 3:
Cumulated YTD ServiceRate = VAR vShipments = CALCULATE( SUM( 'KPI data'[Value] ), 'KPI data'[Data] = "Shipments" ) VAR vShipmentsYTD = CALCULATE( SUM( 'KPI data'[Value] ), 'KPI data'[Data] = "Shipments", DATESYTD( Calendar[Date] ) ) VAR vShipmentDelayYTD = CALCULATE( SUM( 'KPI data'[Value] ), 'KPI data'[Data] = "ShipmentDelay", DATESYTD( Calendar[Date] ) ) RETURN IF ( vShipments <> BLANK(), vShipmentsYTD - vShipmentDelayYTD )
You can find my file here:
In general you have to be aware that most of the results are coming from the context. So you don't say "Shipment for January + Shipment for February", you create the measure and then you use the month names as filter context for the formula. I know that's hard at the beginning and a totally different approach than Excel. Once you're used to that it's magic.
Thank you very much for your feedback and detailed solutions. I created the formulas in my real report under DQ mode, but when I add them in the report, a window poped up like below:
Is it due to the DQ mode? It works in my sample file entered manually, but it doesn't work in the pbix file under DQ mode. Can you help me with some solutions ?
Hey @EZV12 ,
the column you want to summarize is in a text format. In order to calculate with that column, you have to change it to a number.
I would strongly recommend to do that already in your data source.
If that's not possible, you can try to convert it with the CONVERT function. But that doesn't make sense in my opinion.
Please refer to this related documnent:
Community Support Team _ Eason
Check out new user group experience and if you are a leader please create your group!
Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.
Click here to read more about the July 2021 Updates