cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
EZV12
Helper III
Helper III

DAX formulas requests - please help

Dear all,

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:

EZV12_0-1621251582072.png

 

Many thanks in advance for your help.

Best regards

Rachel

8 REPLIES 8
selimovd
Community Champion
Community Champion

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.

 

Best regards

Denis

Hello Denis,

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:

EZV12_0-1621261417384.png

Please help! Thanks.

Rachel

selimovd
Community Champion
Community Champion

Hello @EZV12 ,

 

yes, those descriptions were more clear.

I understood that you need a result like that:

selimovd_0-1621275664838.png

 

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:

https://www.swisstransfer.com/d/71431f2c-3490-4edb-a33f-15e31867b1fd

 

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.

 

If you need any help please let me know.
If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍
 
Best regards
Denis
 

Dear Denis,

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 ?

Thank you.

Rachel 

 

EZV12_0-1621340516386.png

 

selimovd
Community Champion
Community Champion

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.

 

If you need any help please let me know.
If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍
 
Best regards
Denis
 

Hello Denis,

I tried but another 2 windows poped up:

EZV12_0-1621343328574.pngEZV12_1-1621343343807.png

 

Hi, @EZV12 

Please refer to this related documnent:

https://community.powerbi.com/t5/Desktop/Changing-Data-Type-of-a-Column/m-p/1543656 

Best Regards,
Community Support Team _ Eason

Thank, Eason, I will have a look!

Best regards

Rachel

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

Get Ready for Power BI Dev Camp

Power BI Dev Camp - September 30th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!

PowerPlatform 768x460.png

Microsoft Learn

Check out our new Discover Your Career Path blog post series and get all the details.

Top Solution Authors