Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago

Help Converting an Excel countifs function into Power BI

Hi all,   I am new to the forum and new to Power-Bi and I really need a little help.   I have been given an excel sheet and asked to convert it to Power-Bi. I am really stuck with 1 formula in pa...
Ashish_Mathur's avatar
Ashish_Mathur
Super User
8 years ago

Hi Anonymous,

 

This should be possible with a calculated column formula.  Share the link from where i can download your file.

Anonymous's avatar
Anonymous
Not applicable
8 years ago

Hi,

 

Thanks guys for taking the time to have a look and try to help :)

 

 

Please try this link to the excel file. https://1drv.ms/x/s!AraHh-vwAl8dgU2WZDIaPRQmV0Bq

 

and this link for the extract from Powerbi. https://1drv.ms/x/s!AraHh-vwAl8dgVE6xMUtsW9SsAQH   

 

I have managed to add an order line count. i still need the last 2 columns from the excel sheet adding to PowerBi .

The Table i am using in PowerBi is called Orderwell Summary.

 

 

 

 

  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    Community Champion
    8 years ago

    Hi Anonymous

     

    Try this calculated column for "number of single picks in aisle for single item orders"

     

    number of single picks =
VAR check =
    COUNTROWS (
        FILTER (
            ALL ( Orderwell_Summary ),
            Orderwell_Summary[Column1] = EARLIER ( Orderwell_Summary[Column1] )
                && Orderwell_Summary[no. order lines] = 1
        )
    )
RETURN
    IF ( check >= 1, check, 0 )

    • Zubair_Muhammad's avatar
      Zubair_Muhammad
      Community Champion
      8 years ago

      Hi Anonymous

       

      Try this calculated column for "duplicate pick locs for single item orders"

      duplicate pick locs =
VAR check =
    COUNTROWS (
        FILTER (
            ALL ( Orderwell_Summary ),
            Orderwell_Summary[Pick-Loc] = EARLIER ( Orderwell_Summary[Pick-Loc] )
                && Orderwell_Summary[no. order lines] = 1
        )
    )
RETURN
    IF ( check >= 1, check, 0 )

       

       

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      Hi,

       

      That seems to work for an overall count on all records but when i use my closed date filter / slider it does not filter this new column qty. it still reads the figure for all data.

       

      Therefore one item will have a figure like 2192 next to it when i am expecting between 1 and 5 for a given day? 

       

       

       

       

       

       

      • Ashish_Mathur's avatar
        Ashish_Mathur
        Super User
        8 years ago

        Hi,

         

        If you wish to use a slicer, then no calculated column formula approach would work because a slicer selection does not cause a calculated column to recalculate.  Calculated columns are recalculated only at the time of opening the file or when clicking on Refresh.

         

        It will help a lot if you explain the data, the business problem and show your expected result.