Forum Discussion

PoornimaV's avatar
PoornimaV
New Member
3 years ago

Convert Tableau LOD calculation into Power BI

Can anyone help me to rewrite the below Tableau calculation into Power BI please? I have tried below PBI calculation but it doesnt return the desired outout. I have also attached tableau report screenshot for better understanding what the calculation looks like

 

 

 Tableau Calculation:

Count Group = {fixed [Wholesale Parent],YEAR([Post DATE]),[Location Sub Type] :IF countd([Location#]) = 1
THEN '1 Location'
ELSEIF countd([Location#]) > 1 AND COUNTD([Location#])<= 5
THEN '2-5 locations'
ELSEIF countd([Location#]) > 5 AND COUNTD([Location#]) <=10
THEN '6-10 locations'
ELSEIF countd([Location#]) > 10 and COUNTD([Location#]) <= 20
THEN '11 - 20 Locations'
ELSE
'20+ Locations'
END }

 

Power BI Calculation:

count grp =
var _year = CALCULATE(SELECTEDVALUE(Datetable[Year]))
var _acountgrp = CALCULATE(DISTINCTCOUNT(LOCATIONS[LOCATION_NUM]),FILTER(Datetable,Datetable[Date].[Year] = _year))
var _group = CALCULATE(SELECTEDVALUE('Location Group'[Group]))

RETURN

IF(AND(_acountgrp = 1, _group = "1 Location"), _acountgrp,
IF(AND(_acountgrp > 1, _acountgrp <= 5 &&  _group = "2-5 Locations"), _acountgrp,
IF(AND(_acountgrp > 5, _acountgrp <= 10 && _group = "6-10 Locations"), _acountgrp,
IF(AND(_acountgrp > 10, _acountgrp <= 20 && _group = "11-20 Locations"), _acountgrp,
IF(AND(_acountgrp > 20, _group = "20+ Locations"),_acountgrp )
Need Help

3 Replies

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    3 years ago

     

     

    count grp =
var _year = SELECTEDVALUE(Datetable[Year])
var _acountgrp = CALCULATE(DISTINCTCOUNT(LOCATIONS[LOCATION_NUM]),Datetable.[Year] = _year)
RETURN SWITCH(TRUE(),
_acountgrp = 0, "no Locations", 
_acountgrp = 1, "1 Location", 
_acountgrp <= 5, "2-5 Locations",
_acountgrp <= 10, "6-10 Locations",
_acountgrp <= 20, "11-20 Locations",
"20+ Locations")

     

     

    • PoornimaV's avatar
      PoornimaV
      New Member
      3 years ago

      Thank you so much for the response! The above calculation is a measure however i cant add measure to the rows right in a matrix table. if you look at the tableau report screenshot that is attached in the beginning on this post, it has count grp (group category) on the rows based on the condition. in the screenshot which is attached to this message i created a table "Locations" with just one column (Group) having 5 rows using "Enter Data " with the count grp (group categories) and to this when i drag the measure which you shared it is returning "no locations" which is group category name but ideally it should be returning distinct count of "wholesale Parent Name". Is there a way to create the group category name with If, else statement by distinct counting the field 'wholesale parent name?' Please let me know if you need further clarifications