Forum Discussion

rhl94's avatar
rhl94
Advocate III
6 years ago
Solved

Create benchmark for specific categories

Hi   I am looking to create a benchmark number that is based on region. My measure looks like this and works perfectly when a single company name is selected.   Region bench = VAR Region =...
Need Help
  • zoloturu's avatar
    zoloturu
    6 years ago

    Give a try with this one

    Region bench =
VAR __company =
    VALUES ( Company[name] )
VAR __region =
    IF (
        HASONEVALUE ( Company[name] );
        CALCULATE (
            SUM ( Company[dss_employee_count] );
            FILTER ( ALL ( Company[name] ); Company[name] = __company )
        );
        BLANK ()
    )
RETURN
    CALCULATE (
        [Skadeskroner];
        FILTER ( ALL ( Company ); Company[dss_employee_count] = __region )
    )

    Regards,
    Ruslan Zolotukhin (zoloturu)
    BI Engineer at Akvelon Inc. / Kharkiv Power BI User Group Leader / DAX & Power BI Trainer
    -------------------------------------------------------------------
    Did I answer your question? Mark my post as a solution!
    It was useful? Press Thumbs Up!

    You are from Ukraine? If yes then welcome to Power BI User Group - KhPUG website. Other country? Check and find proper one - Power BI User Groups

zoloturu's avatar
zoloturu
Memorable Member
6 years ago

Hi rhl94 ,

 

Try this one:

Region bench =
VAR Region =
    IF (
        HASONEVALUE ( Company[name] );
        LOOKUPVALUE (
            Company[dss_employee_count];
            Company[name]; VALUES ( Company[name] )
        );
        ""
    )

RETURN
    CALCULATE (
        [Skadeskroner];
        ALL ( Company );
        Company[dss_employee_count] = Region
    )

Regards,
Ruslan Zolotukhin (zoloturu)
BI Engineer at Akvelon Inc. / Kharkiv Power BI User Group Leader / DAX & Power BI Trainer
-------------------------------------------------------------------
Did I answer your question? Mark my post as a solution!
It was useful? Press Thumbs Up!

You are from Ukraine? If yes then welcome to Power BI User Group - KhPUG website. Other country? Check and find proper one - Power BI User Groups

rhl94's avatar
rhl94
Advocate III
6 years ago

Hi zoloturu 

 

I've tried to wrap an if statement but with no luck. Returns same error as previous image. It seems like it somehow still calculates the 

  • zoloturu's avatar
    zoloturu
    Memorable Member
    6 years ago

    rhl94 ,

     

    For testing purpose, write two measures in SSAS:

    MeasureTest = HASONEVALUE ( Company[name] )

MeasureTest2 = HASONEFILTER ( Company[name] )

    Then deploy it to a server of course.

    After select a single company at slicer, you should receive TRUE in both measures.

    Then remove filters and both should be FALSE.

     

    Post the results here.


    Regards,
    Ruslan Zolotukhin (zoloturu)
    BI Engineer at Akvelon Inc. / Kharkiv Power BI User Group Leader / DAX & Power BI Trainer
    -------------------------------------------------------------------
    Did I answer your question? Mark my post as a solution!
    It was useful? Press Thumbs Up!

    You are from Ukraine? If yes then welcome to Power BI User Group - KhPUG website. Other country? Check and find proper one - Power BI User Groups

     

    • rhl94's avatar
      rhl94
      Advocate III
      6 years ago

      Works as expected.

       

      Picked a blank for anonymous reasons

       

      • zoloturu's avatar
        zoloturu
        Memorable Member
        6 years ago

        Give a try with this one

        Region bench =
VAR __company =
    VALUES ( Company[name] )
VAR __region =
    IF (
        HASONEVALUE ( Company[name] );
        CALCULATE (
            SUM ( Company[dss_employee_count] );
            FILTER ( ALL ( Company[name] ); Company[name] = __company )
        );
        BLANK ()
    )
RETURN
    CALCULATE (
        [Skadeskroner];
        FILTER ( ALL ( Company ); Company[dss_employee_count] = __region )
    )

        Regards,
        Ruslan Zolotukhin (zoloturu)
        BI Engineer at Akvelon Inc. / Kharkiv Power BI User Group Leader / DAX & Power BI Trainer
        -------------------------------------------------------------------
        Did I answer your question? Mark my post as a solution!
        It was useful? Press Thumbs Up!

        You are from Ukraine? If yes then welcome to Power BI User Group - KhPUG website. Other country? Check and find proper one - Power BI User Groups